By David Gracey
Much of my workday is spent in Excel so I’ve compiled a few of my frequently used tasks that you may find helpful. I’m using Microsoft Excel 2013, so all the tips are using those settings. These options are also available in earlier version of Excel but will be located under different tabs.
Print headers and footers: Using headers and footers is a great way to insert important information into your spreadsheet. When it’s time to print the spreadsheet, this information is updated and included on the printed form. This can be very handy. Let’s say, for instance, you include the path and filename of the spreadsheet in a footer. Then you print out and then months later you find the printout. Using the file and pathname that is on the footer, you can quickly locate the spreadsheet without having to remember where on your computer it was stored. This is particularly helpful in a business setting where there are thousands of documents and spreadsheets stored on the network servers. And because the footer uses a variable for the pathname, if the spreadsheet moves to a different location, the footer will automatically be updated with the new location.
Add a Comment: Many times when I am working in a spreadsheet, I want to add a comment of sorts which will provide relevant information. Sometimes I can get away with creating a column that has the comments. However, there are times when I either don’t want the comment to appear on the spreadsheet or the placement of the comment will give the spreadsheet a weird appearance. Adding a comment is very simple: right-click on the cell you want to add the comment, then choose INSERT COMMENT and a small beige window will popup showing your name and prompting you to begin typing your comment. The reason it adds your name to the comment is so others who might work on the spreadsheet will know who added the comment. Once the comment is added, a small red triangle appears in the upper right corner of the cell quickly identifying any cells that contain comments.
Filter data: if you regularly use a large spreadsheet to keep track of inventory or lists of some sort, then enabling the filter function within Excel is very handy. In our example, we have a spreadsheet that contains a few pieces of information in the cells. And let’s also assume the spreadsheet has several hundred rows which would make locating a particular entry time consuming. If only Microsoft had included a quick way to filter the spreadsheet down to the content I’m looking for. Ah, but they did. The first step is to turn on filtering. Do this by clicking the “SORT & FILTER” drop down button on the HOME tab. From that drop down, enable filtering by clicking the FILTER option.
Once you’ve enabled filtering, you’ll notice small drop-down arrows on the top of each column on your spreadsheet. To filter your spreadsheet, simply click the drop-down arrow on your column and then choose the value you want to filter on. Once you’ve selected that, the content of your spreadsheet that doesn’t match your filter will be hidden from view. Return to the normal view by clicking the drop-down arrow again and selecting the SELECT ALL box and, voila, all your data is back!
Bonus tip: I might be the last person on the planet to know this tip and you probably already know this trick but a co-worker showed me this and it has changed my Excel life. For the past 20 or so years I’ve used Excel, I’ve always defined a print range if I only wanted to print out part of a spreadsheet. This option is located under the PAGE LAYOUT tab then selecting PRINT AREA button. This will permanently set the print range to the cells that you have highlighted. From there you click the FILE tab, then PRINT and print your selected range. A quicker way to do this is to highlight the range you want to print, then from the FILE tab select PRINT. On the main print screen, locate the SETTINGS drop down and choose PRINT SELECTION. When you print a lot, avoiding the extra clicks I was doing ends up saving a lot of time. Sometimes the simple tips are the best.
I liked the bonus tip that you posted about how I can go to PAGE LAYOUT and make sure that I only print out a certain part of a spreadsheet. That will be really useful in the future since there have been several times when I needed only one section of a spreadsheet printed. This will help me save money on paper in the future, so I’ll use this function on Excel from now on.