One of the most important aspects of being an accountant is learning how to complete tasks in the most effective and efficient way possible. The biggest tools to doing this is through possessing solid excel skills. Excel is the tool of the trade!
There are numerous excel functions that are useful to accountants, but I’d like to share the ones that I personally have used over and over again throughout my daily work. With that, I’d break those skills into two sections. 1) Formatting and 2) Formulas.
FORMATING
The Home Tab – Mastering the basics
- Wrap Text – Will expand text box cells to make lengthy text fully readable in one cell, instead of being cut-off or extended into other cells.
- Merge & Center – Use this to align text right, left, center, ect. And to combine multiple cells to make one new cell.
- The “Number” section – this is an important tool to format numbers and other words in an excel file. Typically, accountants use accounting format for numbers which can easily be done by selection a number in your excel document, and hitting the comma icon to format it into a number with commas and 2 decimal places.
- Font Section – The Icon with the small box and dark line at bottom of box can help to create underlined cells. This is typically used more than the underline button next to the italicized text icon. Using these borders will come in handy for presentation.
Insert Tab – Pivot Tables
- Pivot Table Function – The most important use of the insert tab in excel for accountants in my experience is the use of pivot tables. Pivot tables allow you to take large chunks of data and summarize it in many different ways. There are many good videos and courses out there that can help someone interested, learn more and become proficient at pivot tables.
Data Tab – Lots of great tools here!
- Filter – Use the filter tab to create searchable categories for each column of data in an excel file. This is super helpful in searching large data sets.
- Group/Ungroup – Can be useful to expanding and collapsing/hiding a portion of data in rows or columns within an excel spreadsheet. Also a super useful tool!
- Text to Columns – This allows a person to separate data in a cell. For example if you have a number and a description in a single cell, test to columns can separate that information into two separate cells/columns.
Review Tab – Because Why Not?
- Spell Check – Friendly reminder, we’re accountants, who needs to know how to spell, right? But just incase, let’s make sure we know how to spell check an excel document to make sure whatever we work on stays professional.
FORMULAS
Now that we’ve covered a few basic formatting capabilities in excel, let’s talk about a few common formulas.
- Rounding (=ROUND) – Use this formula to round numbers to the nearest dollar, or other decimal place
- Sum & Subtotal (=SUM, =SUBTOTAL) – Use to sum up information
- V Look-Ups (=VLOOKUP) – Allows you to search data from another spreadsheet based on a specific criteria, and pull that information into your current spreadsheet.
- Concat/Concatenate (=Concat, =Concatenate) = This is helpful when formatting data. It allows you to add cell information together in one cell. For example if you had two numbers in two different cells, you could create a new cell and connect those numbers together i.e 123 & 256 could become 123256 or you could add other symbols and make it 123-256. Super useful when formatting data.
- IF Formula (=IF) – as described by excel, this lets you “check whether a condition is met, and returns one value if true, and another value if false.
And there you have it! These are some of the formulas, functions, and other tools that I use on a daily basis as an accountant. If you are new to excel, learning how to use a few of these will help make you successful in your current or upcoming accounting job.