Data Analysis in Excel - 12 Tips & Best Practices
How to get the most out of a limited tool
Our global financial system is essentially built with duct tape, glue, and Microsoft Excel. For non-computer/data science people, Excel is often their initial introduction to data analysis. This was the case for me. In fact, I spent the first three years of my career entirely in Excel. It is a great tool, especially when you are new to data and are working with a smaller dataset. In this article, and those to follow, I’ll walk you through what to do (and not to do) to unleash the power of Microsoft Excel. In addition, any resources used below will be published to my Github.
Before we get going, a brief word on the perspective I am writing from. I grew my skills from Excel into a knowledge base that has served me in R & SQL. The practices outlined below are imbued with lessons learned on this journey. Small steps, like naming your variables and notating your sources make all the difference. Keyboard shortcuts used below are applicable to Excel for Windows. Excel for Mac shortcuts can be found here.
Excel is the standard spreadsheet application used by businesses around the globe. Competitors such as Apple’s Numbers exist, but Excel is the most widely used. Excel can store multiple worksheets in a single file called a workbook. All the sheets within a workbook can “communicate” with each other through formulas, which leads us to our first best practice:
Name your worksheets (Keyboard Shortcut:
Alt+H+O+R)
Each worksheet in your workbook will house something different. Some will display analyses, PivotTables, charts, or even text. Others may house reference tables or raw data.
Note your data sources
Every data source should be noted. Transparency lends credibility to anything you are working on, and diligent notation allows you and others to revisit the work at a later date. I generally write sourcing notes in Cell A2 of each data worksheet. This is right below where I indicate the file’s update date.
Leave a record of when data was refreshed
I store my refresh date in Cell A1 of the main raw data tab in my workbook. For the sake of transparency, it is best to reference this value on other sheets throughout the workbook. This is much easier when you name your cell references.
Name your variables (Keyboard Shortcut:
Alt+F3)
In every workbook I touch, I name cell A1 of my primary data tab ReportDate. This makes it easier to tell when you last worked on a file, or if it is a regularly produced file when it was last updated. Always update this value right after you drop new data into the file.
Store data in tables (Keyboard Shortcut:
Ctrl+T)
Tables in Excel will always expand their range to accommodate additional records of data1. This saves a ton of time in adjusting formulas or increases processing speed if you opted to reference a sheet’s entire column. Additionally, your formulas will now indicate the names of your columns, making it easier for you or others to trace.
Name your tables (Keyboard Shortcut:
Alt+JT+A)
For the same reason we name our variables, we want to name our tables. This cleans up our formulas to the point where your average user can interpret most of what you are doing. If they want to trace summary data back to the raw data sheets, make sure it is clear what they’re looking at.
Differentiate between sourced and calculated data
The column headers of each data table serve as a guidepost to you and to the end user. All sourced data should be to the left, with all calculations to the right. This makes refreshing the table easy and intuitive. In addition, I use different colors in the header to indicate if data is pasted in or if it is formula-based. For sourced data, I personally use Excel’s Blue, Accent 5, Lighter 40% (#9BC2E6) with bold black text. For calculations I use Gold, Accent 4, Darker 25% (#BF8F00) with bold white text.
Format numbers appropriately (Keyboard Shortcut:
Alt+H+FM)
Make sure that the data stored in tables or used in analyses is in an intuitive format. Dates, when displayed as serial numbers, are useless, as are percentages when displayed as dollars and cents.
Use consistent formatting (Keyboard Shortcut:
Ctrl+Shift+V)
Your workbooks will look more professional if you are uniform in how you structure the file and format the finer points. If you’re starting a new workbook it is a great idea to paste formatting from a file you’ve already built. Paste special is a great resource to choose what you want to copy over.
Store analyses in the front of your workbook, data in the middle, and reference tables in the back
Every sheet you want a standard user to see should be at the front (to the left) of your workbook. I operate under the assumption that the majority of users never look past page one.
Save your workbook on the tab you want users to see first
Building on the point above, go to your first sheet before you click save. This way you can guarantee that the user sees what you want when you distribute the file.
Be receptive to user feedback
I can build a badass Excel file that accomplishes everything I think is worthwhile for a given task. But, guess what? If the user does not like it, they will not use it. If they do not use it it is, by definition, useless. I’d rather build something useful that differs from my initial conception of the file than build a tool that goes to the reporting graveyard.
Our next Data Analysis in Excel article will dive deeper into which formulas to use, and why. For now, please check out the best-practices-1 file on Github. Do you like this content? Would you like to join me on this journey? Please subscribe for free below. Additionally, if you have friends or colleagues that would benefit from these tips, please pass this along to your network!
Alec A. Nathan is the Director of Research and Data Analytics at Sylvan Road Capital. A graduate of the University of Georgia, Alec specializes in real estate investment and data science. Please direct any inquiries to anathan@sylvanroad.com.
Caution: If a data source has less rows than previously, you will need to delete data from the bottom of your table!
Pro tip: Show a count of table records in Cell D1.
helpful!
Great article with some fantastic insight. Thank you!