Excel Time Saving Tips

Excel is a powerful tool that is used for an endless amount of applications. As an accountant, I use Microsoft Excel for all kinds of different things including financial reporting, analyzing data, creating charts, personal finances, and I use the drawing tools to map out my woodworking projects. It is funny that the more ways that I learn to use Excel; I am still able to find new ways to become more efficient with it. Here is my list of Excel tips and tricks that will save you time.

One of the best tips for MS Excel that I can give you is to learn to use keyboard shortcuts because they are a great way to save time. It is much quicker to use a combination of keys rather than taking your hand off of the keyboard and using the mouse to do it. Here are the shortcuts that I find to be the most useful:

Customizing the ribbon in Excel is a great way to become more efficient. The ribbon is located either above or below the toolbar. It came in to existence starting with Excel 2007, before that you could customize any toolbar or create your own. My advice is to add the buttons that you most commonly use to the ribbon. Click on the Microsoft Office button in the top left hand corner of the screen and select “Excel Options.” On the left hand side of the popup window, click on “Customize.” From here, you can add any button that you want to the ribbon. I suggest adding the following plus to your ribbon:

• New Workbook
• Open Workbook
• Save Workbook
• Save As Workbook
• E-Mail
• Set Print Area
• Print Preview
• Quick Print
• Insert Sheet Columns
• Insert Sheet Rows
• Delete Sheet Columns
• Delete Sheet Rows
• Hyperlinks
• Record Macro
• Insert PivotTable
• Freeze Panes

Macros are another great way to save time in Excel. Macros are easy to record and use. A macro is a task or a series of tasks including any keystroke or mouse click that are performed all together. It can than be assigned to run automatically when the spreadsheet it opened or by using a command button.

Using Vlookup to pull data from a table to another area of your spreadsheet or to a new spreadsheet can be a real time saver. Vlookup does have some limitations. First, what you are looking up must be the same in both places. Second, Vlookup will only return the first occurrence of what you are trying to lookup. Finally, Vlookup can only return results in the column being looked up or the columns to the right of it. The Vlookup formula looks like this:

=Vlookup([what is being looked up],[table with column matching what is being looked up all the way over to the column that you want to return the results from],[number of columns to the right of what is being looked up],[true or false])

Most Vlookup formulas will end in false. The false statement tells Excel to return an exact match or return a #Error message. The use of true will find an exact match or the next closest match.

The “Format Painter” button is a great way to copy the format of a cell or group of cells and paste the format over a desired area. All you have to do to use the “Format Painter” button is to highlight the cells with the formatting that you wish to copy and then click the “Format Painter” button. The highlighted cells will look like you copied them. Next, highlight the area that you want to paint the formatting to. One word of warning is that once you let go of the mouse it will paint whatever cells that you have highlighted whether you have highlighted the whole area or not. If you do make a mistake, hit “CTRL + Z” to undo it and repeat the process to try again. The “Format Painter” button is located on the “Home” tab on the left hand side.

Hyperlinks are an invaluable resource in a spreadsheet. They make navigating a large workbook or worksheet much easier. The use of hyperlinks in Excel allows you to link to external documents, internal parts of your workbook or worksheet, or websites. I use them regularly as part of a table of contents for my financial reporting template at work that has well over 100 different tabs. I also use them to link to external files that are related to the current worksheet. To insert a hyperlink, use the keyboard shortcut “CTRL + K”. Alternatively, you can also right-click in the desired cell and select “Insert Hyperlink.” A box will popup and you can change the name that will appear as the hyperlink and add the website address or select the tab and the cell reference inside your current document.

For our financial reporting template at work, we enter all of the data into the back of the file and pull that information forward by linking to the data tabs in the back of the file. This will save you from having to do excess data entry in Excel. For most of those tabs, all I had to do was put in a simple formula to grab that data and pull it forward. I did this by entering an equal sign and then going to the appropriate data tab(s) in the back and click on the cell or cells that I wanted to pull forward. It is important to make sure that you check the data after you enter the formula to make sure that it is right.

Another one of my favorite time saving measures in Excel is to use find and replace. I find that it is great when you are creating a template and it is quicker to copy over an existing linked tab and use find and replace to change the tab where the formula points. You do have to be careful when using this because you can easily change things that you do not want to be changed. It is best to highlight the area that you want to change and then hit “CTRL + H” to bring up the find and replace dialogue box. Type in what you want to find and then type in what you want to replace it with in the corresponding box. Leave the “Within:” drop down box as “Sheet” unless you want to find and replace all throughout your workbook. Searching by rows or columns does not seem to make any difference. I normally leave the “Look in:” drop down box as “Formulas”, but if I am searching for a numeric value and do not find it, then I may try changing this to “Values.”

I hope that these tips for Microsoft Excel will help you to save time. One of the great things that I love about Excel is that the more that I am in it, the more ways that I find to become more efficient.