As any accountant will attest, it’s not uncommon to fall asleep with spreadsheets and gridlines burned into our eyes and memories after a day at the office. Excel is perhaps the most used application of any accountant, and also the most underutilized. Even after graduating with a fancy accounting degree, new accountants can be unaware of several simple and convenient features of Excel that can make our jobs much simpler, more efficient, and accurate.

Tip # 1- Hotkeys

Hotkeys allow you to execute basic actions quickly without reaching for the mouse. I believe every office has at least one accountant who is revered for his or her ability to create complex and even artistic spreadsheets without lifting a finger. With just some quick memorization, YOU could be that accountant! The following is only a short list of commands; I encourage you to spend some time exploring the options on your own!

Alt – Alt is the “home base” for all hotkeys. Pressing the “alt” key will bring up an overlay of shortcut options from which formatting, formula, and review options are accessible. For instance, if you wish to add borders to a table, you can simple press the following sequence of keys to add borders to all cells: Alt + H + B + A

While it may seem cumbersome at first, it is quick to become second nature and you will find yourself formatting on the fly with ease!

Alt + = – Pressing the “alt” and the “=” key at the same time will execute the autosum function from the currently highlighted cell.

Alt + I + R – this action will insert a row at the current cursor location. You can replace “R” with “C” to add a column.

Alt + Tab – Pressing Alt and Tab at the same time will allow you to quickly flip between windows. This is not an excel specific hotkey, but it is extremely useful for flipping back and forth quickly between screens.

Ctrl + C or V – This command is basic, but is a must know. Control and C copies data, while V pastes it.

Tip # 2- Pivot Tables

Pivot tables are invaluable for summarizing large sets of data quickly and in an easy-to-read format. To create a pivot table, your data must be in a table format with headers and data below. Simply select the pivot table option from the “insert” tab and select your data range and the cell where you would like to place the table.

Once your table has been created, use the box on the side to arrange data by filter, column, row, and values.

Filter– This option allows you to filter the table by whichever data you select. For example, you could place the “month” column in the filter box in order to select which months the table will display.

Row – This box lets you choose which data is displayed along the X – axis.

Column – This box allows you to choose the data on the Y – axis.

Value – This box will determine the content in the middle of the table. This box has a variety of options to choose from. Your data can be a sum, count, average, product, or other value for the data you’ve chosen in the X and Y axis.

Pivot tables are great for arranging data, but once you have created a format you are happy with, it can be helpful to copy and paste values (more on that below) in order to manipulate the data more freely with formulas and referencing.

Tip # 3 – Copy/Paste

Tip number 3 may seem basic, but is again a feature that is often overlooked. When copy and pasting data, there is more than one way to paste! When pasting, refer to the “paste” box in the upper left corner for the following options:

Match “ xxx” Formatting – The format matching paste option will allow you to either paste data and keep the format in the destination cell (via match “destination”) or will overwrite the destination cell with the copied cell’s format (via match “source”).

Paste Values – Possibly the most useful special paste option. This option allows you to copy formulas and paste the resulting values rather than copying the underlying formula. This is extremely useful for customizing pivot tables, as noted above!

Paste Formulas – This does the opposite of paste values and preserves the source formula when pasted.

Transpose – A handy, little known tool. Transpose will “rotate” your data so that the columns and rows are switched.

Tip # 4 – Formatting and Manipulation

Everyone knows you can use formulas to manipulate data, but there are plenty of formatting based tools and uncommon formulas that you can add to your arsenal to supplement your “if” statements!

Concatenate – Concatenate(Cell1,Cell2) will take the value in each cell and combine them. For example, “BBQ” and “Sauce” will become “BBQSauce” in the formula cell. (Bonus tip: concatenate a row with a space between your data to separate words “BBQ Sauce”).

Text to Columns – This does the opposite of Concatenate and splits one column into 2 discrete data columns. Text to column can be found on the “Data” tab and is especially useful for working with delimited files, which are large chunks of data separated by a character such as a period or a tab. Text to Columns would separate the text (BBQ.Sauce) at the period and result in two columns (BBQ) and (Sauce). You can choose what character separates your data, so use what works best in your situation!

Conditional Formatting – Conditional formatting is great for highlighting specific data quickly and visibly. From the home tab, you can use conditional formatting to highlight the largest or smallest values in a set, values greater or less than a specific number or between a range, and (my favorite) duplicate values. You can even setup your own formula to highlight any cells that match your formula criteria.

Find and Select – This tool can be found in the upper right hand of the “home” tab. The “Go To Special” is particularly worth mentioning. With this option, you can easily select all blank cells in a column, or all formulas in the worksheet. If you want to delete all rows with blank data, simply “Go To Special”, “Blanks”, and use the “Delete” tool on the “home” tab. Very handy for condensing data!

Filling Data Down – Have you ever needed to add account numbers to a long list of account descriptions in the adjacent cell but don’t want to do it manually? You don’t have to!

First, use the “Go To Special” to find all blank cells in your columns:

 

Then, type in “=” and hit the up arrow key. The formula in my example will be “=B2”.

Press “Ctrl + Enter” and the formula will copy to all of your blank cells that have been selected, which results in a nice, clean column that looks like this:

Excel is a powerful tool that can make your life very easy if you take the time to learn a couple simple tricks. This has been just a brief glimpse of what excel can do to make your job easier, neater, and more accurate. I strongly encourage all accountants to share their tips with coworkers and to explore the capabilities of excel on your own, through trial and error or through any one of the many resources online! There is just about nothing it cannot do! And the good news is, you can even practice in your sleep when your dreams have been taken over by those lovable gridlines.

Ryan Barnes
Thomas Howell Ferguson, PA