While working in Excel or Google Sheets there are a few tips and tricks that we consistently noticed more experienced users took advantage of to visually enhance their reports and dashboards. Mastering Excel or Google Sheets can be hard! There are thousands of different functions, formats, and different ways to do things.
We wanted to pick some of the easier ones to make it LOOK like you are an expert even if you might not be.
The different tools we are going to highlight include - removing gridlines, EOMONTH function, conditional formatting, freezing panes, and finally creating Sparklines. We also have a video at the end of this post walking through how to use each one.
Removing Gridlines
This is one of our personal favorites. Before working on a new financial model or report, we always like to turn off gridlines to give our worksheet that new clean feeling. This will let you build in your own formatting from a blank slate. You can find this under the “view” tab.
EOMONTH
Using the EOMONTH function - stands for “End of Month” - you can automatically get the last day of the month for the given period. By using this function, you can choose the date you want to select the last day of the month for. Next, you can also choose if you want the formula to give you the last day of that current month, or a future or previous month. The benefit is that you can use cell references when building your function and drag the EOMONTH formula across your workbook to always give you the last day of the next month in sequential order - see video for details.
Conditional Formatting
Adjusting your formatting is another way to help your reports stand out. The easy way is to hit the dollar sign, so all of your values show up as monetary amounts. A trick you can use is to go into the formatting options and select for all negative numbers to be formatted in red. This helps the viewer easily find and spot any negative values or balances in the spreadsheet.
Freezing Panes
For larger spreadsheets or financial models it's easy to get lost in the numbers! Freezing panes let you lock the headers and categories of your reports to ensure they are always visible. That way no matter how far you need to scroll, you will be able to tell the category and period it is coming from. You can find this under the “view” tab.
Sparklines
Finally, we are going to cover a lesser-known feature called Sparklines! These are mini graphs that exist only in individual cells. The benefit of using Sparklines is that you can embed them in any financial reports or models without taking up any space. Try using Sparklines on any data you want to quickly visualize trends such as your last 12 months of revenue! This one is a little different between Excel and Google Sheets. For Excel you can find it under the “insert” tab. Where as in google sheets you can enter the formula in the cell you want “=Sparklines(“.
Remember the best way to start learning is to dive in and try it out! Have other tricks you use? Let us know and we can feature them in our next post!
