Excel is known to be one of the most useful tools when it comes to finances. With that being said, it can also be very tricky and confusing. We recently spoke with Pat Rosemas, A&F’s fiscal planning manager, to gather a few of his tips and tricks when it comes to Excel.
- First things first, Google is your friend. There is an answer to “how do you do ‘X’ in Excel” for pretty much anything you can think of.
- LinkedIn Learning, which we all have free access to, offers training programs ranging from beginner to advanced
- Sumif(s), and Vlookup are two of the most commonly used functions used in finance and accounting, while Pivot Tables are one of the most common tools used for aggregating data for reporting and analysis
- Microsoft offers a lot of pre-built Excel templates for all sorts of needs. These generally need to purchased.Free templates can also be found but it’s recommended to only download them from reliable sources, especially if they contain macros
- It’s possible to connect Excel to outside data sources/table (e.g., Access), for developing reports when the outside data source doesn’t have an integrated report building application
- Using Tables can make it easier to manage data sets. They allow for easy formatting, headers remain visible when scrolling down, formulas and formatting copy down when adding rows, and you can create dynamic pivot tables and charts from them (i.e. you don’t need to change the data range every time you add a new row of data)
- If you have a large workbook where all the tabs don’t fit on one screen, right clicking on the left/right arrows in the bottom left corner of the screen will pull up a list of tabs that can be selected for quicker navigation
- If you want to center text, such as a header over multiple columns, but merging cells creates complications, you can format cells to “Center Across Selection” instead
- Under the Formulas Menu option there are drop down menus with functions organized by category that you can use to peruse your options
- “Text to Columns” can be helpful if you’re copying data from a separate source to Excel and it all pastes into one column. Text to Columns can convert the data back into multiple columns.
- You can create a custom “quick access tool bar” if there are functions and/or formatting you commonly use
- If linking cells in a report or model Ctrl + [ is a hotkey to take you to the cell you’re linked to.
- The “Choose” function is something I rarely see used but is useful to build models with multiple scenarios or forecasting methodology options. Index is another one I love that is similar and useful in both financial modeling and reporting.You can also imbed functions within other functions, such as Vlookup within Choose.
While there is a science to Excel, there is also an art to it, so have fun and be creative!