Whether you’re a data analyst or a bike tour guide, you probably find yourself wrangling Excel spreadsheets from time to time – perhaps you’re valuing early stage companies with an Excel model or you’re mapping out the distance and elevation increase of a new trip route. Excel is one of the simpler tools available to manage and analyze data - easy to access for many new to data, the default export format from CRM’s, or even the organizational standard before scaling to a SQL database. What if these messy, boxy spreadsheets could look a bit more attractive and manageable – easier to pinpoint insights and harder to get lost in?
Below is a list of suggestions that will take any boring, old spreadsheet and spiff it up a bit. Take these three steps first:
Hide the gridlines. Do this first! It makes the largest impact and is just unchecking a box in Page Layout or View menus.
Add buffer rows and columns. Right click to Insert an extra column and row to provide “buffers” for the data.
Add a header. Insert a few more rows and add a header to let your viewers (or yourself when you reopen the document) know exactly what you’re looking at. You can format this header using the pre-baked Cell Styles in the Home menu.
Great start. Now, let’s give viewers some directives about what the data represents:
Add data source notes. Where did the data come from? When was it last updated? Make sure to provide this context for other views. I like to make these notes grey italics and add it to the right of the header, or below the table for small data sets.
Make sure data types are formatted appropriately. Money is money, dates are dates, and numbers are numbers. The Number Format drop down in the middle of the Home menu will allow you to choose the data type to match the data.
Bonus Tip: If Excel is being panicky about data types as it sometimes is, copy the data into a text document to clean off old formats and paste back in place using Paste as Values in place of the old data.
For both numbers and percentages, be wary of too many decimal places. Think about it this way: How many are necessary to make a sound comparison and decision? This is up to you, but is something to think about whichever way you go. You can adjust how many decimal places using the Tabs just below the Number Format drop down in the Home menu.
Bonus Tip: Standardize phone and address columns. With find and replace, you can standardize all “Str” “St.” and “Street” to just “St”, for instance. For phone numbers, you can remove all “-“, “(“ and “)” to just “” so that all phones are the expected 10 characters long without any extra jazz. To learn more about cleaning lists, I walk through recommendations and more in this Excel tutorial posted on Github.
We’re not quite there yet, but we’re getting close. Let’s dive into some more specifics around tables:
Use grey lines, rather than black lines. Your gridlines might be gone but you can provide your viewers with easy formatting of the table boundaries. This and the following changes can be made in the Home menu.
Add conditional formatting to 1 to 2 columns. Bar charts and color scales always work well to highlight trends of priority metrics.
Bold and wrap headers to make them stand out. Wrapping text will mean you can keep narrow columns but have longer multi-line headers.
For larger tables, freeze header row and the first column. Put your cursor in the first data point provided in the table, navigate to the View menu and select freeze rows.
Check out what these tricks can do to a simple stock market data:
Now that your table is in great shape – let’s table that for now and move on to tackle charts:
First of all, do not use default excel colors. This is a checklist of things to “do” but this is crucial. To start, the built in colors are unattractive, and if you’re posting your work publicly, it is a dead giveaway that you’ve built this chart in excel. The good news is that there are some decent color schemes provided like Aspect and Slipstream which can be selected in the Page Layout menu.
Bonus Tip: You can build your own using your organization’s colors. Many organizations have design guides with suggested colors and fonts which will be consistent with template slide decks and a familiar touch for your coworkers.
Keep your chart type simple. Line and bar chart do the trick for most basic analysis. Scatterplots can be helpful in comparing two related variables. Don't get to wild, especially with 3D and pie charts. Many believe that pie carts are the worst option available.
Bonus Tip: In google spreadsheets, you can easily convert state and country data into a map.
For line charts and scatter plots, small white points with light colored outlines can help your data pop. Right click on the line chart, select Format Data Series, then in the color menu add white markers with a small border consistent with the line color.
For bar charts, change the bar width. Right click on the bars, select Format Data Series and adjust the series overlap and gap width. For one data point, I like 0% series overlap and 50% gap width to decrease white space and emphasis the chart data. For additional data points, I adjust to -25% series overlap with 75% gap width.
Bonus Tip: For time series data, such as year over year analysis, you may want to add overlap for these bars by making the series overlap positive.
Get fancy with small touches. Explore the Add Chart Element drop down in the top left to add or adjust trend lines, gridlines, and data labels — to list a few. Don’t be shy about adjusting the title, font sizes, background color, and more to add a bit more jazz.
Still thinking about the stock market data, let’s see the impact of these changes.
That’s all for now. Following even these simple steps can any spreadsheets much snazzier. Many of these rules of thumb can be applied to other visualization tools. I’m personally a fan of Tableau, R Shiny and Looker - to list a few. With an abundance of data visualization and analysis tools available, I believe that it is less important to find the perfect tool and more important to maximize the capacity of the (at times, slightly flawed) hammer you’re provided.