The EntreResource blog is written mainly for entrepreneurs but ANYONE who uses Excel will be able to create better looking spreadsheets IMMEDIATELY after reading this article and implementing the 12 straight forward tips I’m about to show you. '
This isn't a vanity contest either. When I say, "good looking" I'm not referring to fancy fonts, color schemes or advanced macros.
Actually, quite the opposite. Simplicity and minimalism is typically the best approach to creating professional spreadsheets, but more on that in a minute.
"You can tremendously boost how people perceive your work by implementing these 12 simple steps."
Even if you know very little about the more advanced features of Excel, you can tremendously boost how people perceive your work by implementing these 12 simple steps.
You will never make a shitty spreadsheet again if you promise to follow these guidelines!
Why Does a Professional Spreadsheet Design Matter?
There are a couple of reasons why you want to make all of your spreadsheets look as professional as possible.
Yes, I'm talking to my entrepreneur readers as well here!
1. It Makes the Data Easier to Digest
The point of Excel is to present large amounts of data in clear and consumable ways. If your data is presented in a sloppy manner, it isn’t going to be as valuable.
"The point of Excel is to present large amounts of data in clear and consumable ways"
So, even if the data is just for your own internal usage, you will get more out of it if it is visually appealing and easy to skim.
Presenting your data clearly is also going to make sure that you don't have misinterpretations of the data which can lead to costly errors.
2. It Makes YOU Look Smarter and More Qualified
Like it or not, people are judging YOU by how you present your data.
Two people could share the EXACT same data but the one who makes it look professional is going to leave a better impression.
If you need to share spreadsheets with your clients or your boss (God forbid) you need to make sure you are sending a message that says, “we handle things in an orderly way.”
People who are perceived as being better at Excel are much more likely to be perceived as better employees. That is just the way of the world.
"People who are perceived as being better at Excel are much more likely to be perceived as better employees"
Luckily, this article is going to let you hack your way to better spreadsheets in about 10 minutes.
The more of these elements that you implement, the better your spreadsheet is going to look.
I recommend that you PRINT THE SUMMARY OF THESE 12 STEPS and keep it handy by your computer.
Note: For this article, I am starting with a really bad spreadsheet (shown below) and implementing the 12 improvements one at a time. The screenshots will get progressively better looking as we go through each step.
Sweet lord! What sort of abomination of a spreadsheet is that?!?!
Whoever made that should not only be fired, they should have to serve jail time for assaulting our eyes!
Ok, so we can only go up from here!
Let's get into the 12 steps that will make that horrible spreadsheet above look more professional.
#1 Correct Your Alignment
For most of us, the natural tendency is to center our text in the cells.
This is not the best way to do this.
By centering some text and not others, you make the content uneasy on the eyes.
Spreadsheets are for skimming over, so making them easy to read is paramount.
Fix: Give your data a strong, left alignment to text and a right alignment to numbers.
This will look boring for now but stay with me. Note: If you have a main title, that is the exception to the rule and should be centered across the data. I discuss this more in step 7.
Give "text" a left alignment <---
Give #s a right alignment -->
#2 Distinguish Your Headings
The headings need to catch our attention before the actual data.
You can use any combination of the following to distinguish your headings:
Bold text (prefereed)
This helps create clear separation from the rest of the data. The correlation is made instantly without the reader struggling too much.
We still have a long way to go, but this is starting to look better now that we can actually distinguish the column headings from the rest of their data.
#3 Use Fewer Colors
The purpose of color is to draw attention to data and make the spreadsheet easier to skim.
Using too much color can have quite the opposite effect especially when the colors are bright and conflicting.
Limit your color usage to two per sheet at the most. The only exception to this would be if you are using some sort of conditional formatting that shows certain degrees (light to dark) but even those should be used only when absolutely necessary.
I recommend you use a light background with a dark font.
I will show you how to use zebra stripes in a later step to make this look even better.
Use a light background and dark font. Aim to use no more than 2 colors per sheet.
If you can't decide on a font or color scheme you can always use one of excel's built in themes.
#4 Leave "Row 1" and "Column A" Empty to Give Your Data Some Space
You don't want your data to feel like it's crammed together.
Leaving a bit of buffer space to the left of the data and at the top will immediately improve the overall look and feel.
You can adjust the dimensions as you like but I recommend that you make them the same size. In the example below, the column and row are both set to "25" and it creates nice symmetry.
#5 Remove Borders
You may want to use borders thinking that they tighten up the data, but in reality, they usually make things more difficult to read.
Remove them and use light zebra stripes instead (more on that soon).
#6 Use Calibri or Arial Font
Don't overthink your font selection. Stick to proven, easy to read sans serif fonts like Calibri or Arial.
Be sure to pick just one font for all of the spreadsheet, don't use multiple. They don't pair well.
#7 Center Your Title (If Applicable) Across All Data
In the original example, we have a title "Home Inventory" and it is left aligned.
We can make this look better by centering the title across all of the rows.
Notice as well that I have left a blank row between the title text and the row with our headers.
A title text isn't always necessary but it is recommended, especially when you have multiple sheets in a single workbook.
#8 Format Row Height and Column Width
You want to make sure that all of your data is visible and that rows and columns are not taking up more or less space than they need.
This part requires a little finesse as all sheets are different.
Here is what my sheet looks like after making a few minor adjustments to the column widths. I felt that the rows were ok as they were.
If possible, avoid wrapping text, but for some sets of data, that isn't possible.
When resizing columns and rows, ask yourself, "does this make the data easier to read?"
#9 Use Simple Graphs and Tables to Display Data When Applicable
Some sets of data are best shown in graphs and tables.
The example shown doesn't merit a graph or table since it is a basic inventory report but you may benefit from using them if you are showing data that includes different time periods or value fluctuations (like a yearly earnings report).
I promised this would be an easy process so I'm not going to cover graphs and tables. They aren't necessary but you may find them valuable (plus they will really impress people if you do them well!)
Use the same formatting rules I've shown in this article for any other elements you add.
#10 Organize Workbooks with Colored Tabs
Ok, you may be wondering, "what is the difference between a workbook and a worksheet?"
A workbook is a collection of all of your worksheets.
Everything I've shown you so far represents a worksheet. Multiple worksheets would be held in a workbook. A workbook would be just one file with multiple tabs for each sheet.
One lesser utilized feature is the ability to add colors to these tabs. It is definitely not mandatory, but it does give a nice, professional appearance and highlights which sheet is being viewed at any given time.
In the example below, you can see 3 different worksheets with 3 different colors.
Rule #3 (use fewer colors) doesn't apply to your tabs. Get all of your colors out here!
Changing the colors is very easy.
Navigate to the worksheet tabs, right click, choose "tab color" and pick from the available options or add your own. You can even get really wile with it and add pictures (although I advise against it because it will make your file size large and it is very corny).
#11 Strategically Freeze Header Rows for Large "Scrolling" Data Sets
When you have a lot of rows, your headings will disappear when you scroll down.
This defeats the purpose of the headers in the first place!
We can fix this by freezing the row with the headers.
"Freezing" just means that a row or column will remain visible if you scroll around the spreadsheet.
Luckily, freezing panes is easy.
- Select row or column directly below the row or column you wish to freeze. In this instance, I have selected row 2 and plan to freeze row 1.
- With the entire row selected, navigate to "Window" in the top menu bar.
- Select "Freeze Panes" and you will have frozen the desired row or column.
Freeze header rows when your worksheets require readers to scroll down.
#12 Add Light Zebra Stripe Format to Rows Below Headers
Starting with the first row below the headings, you'll want to add a light fill across all the data.
Then, we leave the next row blank and repeat the fill on the row below that one.
We do this all the way down the data.
Some of your spreadsheets will have hundreds or even thousands of rows so doing this manually isn't ideal.
Here is how to add zebra stripes to your rows without doing each row one by one.
I promise, this is the most complicated part of this entire process. Take the time to learn it once and benefit from it forever!
This is easiest to learn in a video, so I've made a quick one below showing the process. I've also outlined it below.
The process is slightly different across platforms, but the concept is the same.
Step #1 Select All of the Rows That Need Formatted
Again, don't do this one by one, click on the first row and then press the down arrow button until all the rows are highlighted.
Unless there are breaks in the rows (there shouldn't be) one click should get you all the way to the bottom.
Step #2 With the Rows Highlighted Select "Conditional Formatting"
Locating this varies based on which version of Excel your using.
If you're using Google sheets, you can find the conditional formatting options under the "Format" tab.
In traditional Excel, you can find the conditional formatting option from the Home ribbon
Step #3 Select "New Rule"
Step #4 Use "Custom Formula Is" and paste =MOD(ROW(),2)
Step #5 Choose the Fill Color (Light Gray Is Best)
We have now gone from this hard to follow worksheet...
To this glorious one!
There you have it, we've now gone from a terrible sheet to a beautiful, easy to read one.
If you found this article helpful, please share it with your friends and co-workers!