Quick Excel Tips & Tricks: Part 2

In the second installment of Stephanie Chastain’s “Quick Excel Tips & Tricks” webinar, she shared with us how to create fun spreadsheet additions. Check out my step-by-step guide on Stephanie’s “Easter Egg” tricks, and learn about topics like changing grid line colors and transforming the shape of comments in Excel!

---

SESSION 2 TABLE OF CONTENTS

CHANGING LINE COLORS

• Changing Gridline Colors
• Changing Border Colors

USING COMMENTS

• Adding a Comment
• See All Comments
• Changing the Shape of Comments

USING AUTOFILTERS

• Turning on Autofilters
• Turning off Autofilters

USING THE FORMAT PAINTER

• Apply Formatting

FREEZING HEADINGS:

• Freezing Headings

CREATING A PIVOTCHART

• Creating a PivotChart

---

CHANGING LINE COLORS

• CHANGING GRIDLINE COLORS (6:45)

Gridlines are what help you see each individual cell. To change their color:

1. Click on the File tab.
2. Select Options.
3. Click on the Advanced tab.
4. About halfway down, you will see a menu beside the “Display options for this worksheet” section that says “Sheet1” and has a down arrow. When you click on the down arrow, you have the option to apply your color changes to the entire workbook (the top option) or individual sheets.
5. Where it says Gridline Color, click on the down arrow and select a color option.
6. Click ok.

 

• CHANGING BORDER COLORS (9:30)

There is a difference between grid lines and borders. Gridlines help you see the cells while working on your screen, while borders are what will show when you print your spreadsheet. Gridlines look like dotted lines versus the solid lines of borders. To add color to your borders:

1. Highlight the cells that you would like to add borders to.
2. While on the Home tab, select the Border button. (It’s the white square divided into four smaller squares).
3. If you already have a Gridline color selected, click on the All Borders option to add that grid line color to your border. Click ok.
4. If you do not have a Gridline color selected, click on the Line Color option to choose a color for your borders. Select your color, and click ok.

Note: Stephanie also reminds us that anytime “marching ants” (aka dots) appear and highlight your text (for example, when copying something), you can always hit Escape to make them disappear.

---

USING COMMENTS

Comments are useful for adding notes to pieces of data in your spreadsheet. Stephanie reviews how to add comments, turn off comments, and change the shape of comments.

• ADDING A COMMENT (12:22)

To add a comment to a cell:

1. Right-click on the cell you would like to add a comment to.
2. Select Insert Comment.
3. Your name will appear bolded in a square, showing that you the one adding the comment. (This is useful for when multiple people are working on a file). Add your comment.
4. You’ll know your comment was added when you click out of the comment, and in the cell you added the comment to, there is a red triangle added in the top corner. You can click on the cell to see the comment again.

If you want to hide your comment, simply right-click on the cell with the comment and select Hide Comment.

 

• SEE ALL COMMENTS (13:42)

Let’s say you are working on a spreadsheet with a large number of comments. To view them all at once:

1. Click on the Review tab (located between the Data and View tabs at the top of your workbook).
2. Select Show All Comments.

If you want to hide all your comments, click on Show All Comments to turn them back off.

 

• CHANGING THE SHAPE OF COMMENTS (14:14)

Note: When a comment is inserted, you are essentially inserting an image.

1. When you click on the edge of your comment, you will see a shape in your quick access toolbar (in the top left-hand corner of your workbook) that looks like a wide arrow. When you hover over the button, it will say “Change Shape.”
2. Click on the down arrow to the right of the shapes and select your new shape.
3. Click and drag to expand or contract your “image” (aka comment) and make it the size you want it to be.

---

USING AUTOFILTERS

Autofilters are useful when you have a lot of data in your spreadsheet and need to find a specific piece of information quickly.

Note: If you are using autofilters, it’s important to make sure you have headers in your top cell.

• TURNING ON AUTOFILTERS (17:22)

1. Click on Row 1 where your headings are.
2. Click on the Data tab (located between the Formulas and Review tabs at the top of your workbook).
3. Select the Filter button (located in the Sort & Filter section).
4. You will see down arrows appear beside your headings. The down arrows allow you to search for data. When you click on them, you can then sort data alphabetically, by color, by scrolling, or by searching. If you unclick Select All, you can then individually choose which data you would like to see appear in your spreadsheet.
5. You will know that your data has been filtered when the down arrow beside the heading changes to look like a filter.

 

• TURNING OFF AUTOFILTERS (19:23)

1. Click on the filter icon next to the heading name.
2. Click on Select All.
3. Click ok.

This will bring all your information back onto the spreadsheet, unfiltered.

---

USING THE FORMAT PAINTER

Formatting is how something looks on the screen or how it’s going to look when you print it out.

• APPLY FORMATTING (21:11)

1. First, select the cells you wish to format. You can highlight the cells you would like to format, or if you want to apply the formatting to everything that is entered in a row (a common practice), you can just click the number beside it.
2. From there, you can apply formatting by using tools located on your Home tab (bold, font size, text color, borders, etc.)
3. The Format Painter will then copy any formatting you have applied to the cell and allow you to paste it into another cell. Under the Home tab, in the Clipboard section on the left side, select the Format Painter.
4. Then, apply the formatting you just created by clicking on the cells you wish to apply them to. Now, the Format Painter will only apply the formatting once and then turn itself back off. If you want to leave the Format Painter on, double-click on it.
5. To exit the Format Painter, press Escape.

---

FREEZING HEADINGS:

Excel makes it easy to freeze headings so you can keep track of the data you are viewing without having to scroll back and forth to see the column labels.

• FREEZING HEADINGS: (24:55)

1. Remember the “above insertion” rule. Click on the row BELOW the row you want to freeze.
2. Click on the View tab (located to the right of the Review tab at the top of your workbook).
3. Select Freeze Panes.
4. You can choose the Freeze Top Row option or select Freeze Panes.

If you need to unfreeze panes, go back to the View Tab, select Freeze Panes, and then select the Unfreeze Panes option. And remember, you can freeze as many rows as you want; just make sure if you are freezing more than your headings to select the Freeze Panes option rather than Freeze Top Row.

You can also freeze columns by selecting that option!

---

CREATING A PIVOTCHART

Note: First, Stephanie reminds us how to add things up quickly in Excel. Click on the cell below where you want your data to be added, stay on the Home tab, and click on AutoSum (in the right top-hand corner). Press Enter. Then, you can use the autofill handle and highlight cells all the way across to add up totals for everything you need instead of adding up each column individually.

•  CREATING A PIVOTCHART (27:19)

1. First, make sure you are in your data.
2. Click on the Insert tab (located between the Home and Page Layout tabs located at the top of your workbook).
3. Select the chart you want.
4. Once you select your chart, every aspect of the chart that shows is just an individual element. This means you can click on different aspects (for example, a bar in the bar chart) and customize your chart (for example, delete elements you don’t want in the chart anymore).

Stephanie covers the ins-and-outs of charts in greater detail in her instructor-led UTC courses.

---

ABOUT THE AUTHOR:

Hi! My name is Marah Whitaker (think Laura with an M). I am the Marketing Assistant for UTC Center for Professional Education. During the workday, I spend time writing blog posts, creating content for social media, developing email campaigns, and building relationships with our customer base. During my free time, you can find me getting lost in a good book, having spontaneous dance parties, playing piano, and going to Buffalo Wild Wings on Wing Night. Professionally and personally, I aspire to live by the Mr. Feeny quote, “Dream. Believe. Try. Do Good.” I strive to use my passions to serve others and contribute positively to the world around me. 

Connect with me on LinkedIn.