Formatting: a few basics

(All my lessons are taught using Excel 2010. Excel 2007 should be very similar functionally, but anything earlier than that will be completely different.  If you’re using an older version of Excel, you may want to think about upgrading, you’re missing out on a lot of great things!)


A major part of making a spreadsheet that works well is formatting. It’s not critical to the technical functioning of your spreadsheet, but it helps with the most important part of spreadsheet creation: Being able to read it later.  


When you are working with a small spreadsheet, formatting is less important than with a larger spreadsheet. But as your data grows, it can be a little difficult to work your way around a table of data if it doesn’t have some logical formatting conventions to help you out. Some of this might seem obvious, but believe me, one of the biggest impacts you can make, especially when sharing a file with another person is to have a well organized, and well formatted spreadsheet.

For the next few lessons, I’ll use a basic sales list as a starting point.  I have a spreadsheet with six columns: Order Number, Order Date, Product, Quantity, Unit Price, Total Price.  Without any formatting, it looks something like this:

Number Formatting:
The most important piece of formatting you can do is number formatting. Large numbers can often be hard to read and it can be hard to know whether something is a dollar amount or quantity, or date, etc.  So we’re going to start out by formatting some of our numbers in the sample spreadsheet. Number formatting options are  seen on your home tab in the Number section. 

 
In my screenshot above, I’ve highlighted a few things.  The red box shows you where you can find the number formatting options.  The purple box outlines some of the quick formatting options you have.  These can quickly put your numbers into the format you want with a single click. The dollar sign will format your numbers as dollars, with 2 decimal places and commas every 3 digits. The % sign will format your number as a percentage with 2 decimal places. The comma will format your number with comma separators and two decimal places.  The two symbols on the right side will increase or decrease the number of shown decimal places. If you click the drop down menu highlighted in blue, it will give you some additional options for quick number formatting as seen below.

If none of these work for you, you might want to click the small icon in the green box in my above screenshot. You can get to the same place by clicking on “More Number Formats” in your formatting drop down.  This will bring up a number formatting window that will give you a lot more options for number formatting.  In the screenshot below, you can see the orange highlighted area on the left, where you can pick your basic category of numbers, and then you have additional options on the right specific to the format that you chose.

So let’s start formatting the numbers in our spreadsheet.  Let’s start from the right side and work our way backwards.  The last two columns are Unit Price and Total Price.  These are easy to choose a format.  Let’s use the currency format from the quick options.  Select all the number cells that you want to format, and click the dollar sign. This formats our numbers with a dollar symbol, two decimal places, and comma separators.

For the Quantity Column, let’s use the comma button. Select all that data, and click the comma. You’ll notice that it adds two decimal places to the end of the numbers:

Since all my quantities are whole numbers, let’s get rid of the decimals.  With the quantity numbers still selected, click the decrease decimals button twice.  That’s the button furthest to the right in the purple box. That will get rid of my decimal places in the quantity column.




Now we’ll move on to the Order Date and Time Column.  You’ll notice in my screenshot, these don’t look like dates at all.  If you enter a date into excel (i.e. 10/4/2011) it will automatically format as a date.  But in the background, the data is stored as a number representing the number of days since 1/1/1900.  10/4/2011 is actually stored as 40820.  If you want to add time to that equation, you enter it via decimals. So 10/4/11 5:38 AM is stored as 40820.235.  For formatting this column, if we only want to know the date, we can format this as a Date by selecting Short Date from the Number Format Drop Down box.  If we want to format it with date and time, we’ll need to go into the Number format window.  See my screenshot below for an example of how to format this with the date and time visible.

So Now we’ve formatted all of our Numbers, and our spreadsheet looks like this:

Formatting Borders and Tables:

Let’s make a few more tweaks to the formatting here to make this a little easier to read.  One thing that makes a big difference if you plan to print your table is to add borders to your cells. When you’re looking at the file on your screen, It’s easy to see the different cells, because excel shows lines between the cells, but when you print it, those lines go away, and it can sometimes be hard to see where one cell ends and another begins.  For this next bit of formatting, we’re going to use the Font Section of the Home tab, and we’re going to click the divot (small triangle) next to the borders button.  This will drop down a list of options for creating borders.  

For starters, we’re going to draw a thick border around the entire table. So we’ll select the entire Table, and click the divot, and select Thick Box Border from the drop down. This will create a nice dark border around the whole table. We also want to put dotted borders between cells, but let’s just do that between each row, not between the columns.  To do this, select your whole table again, and click the divot, and select More Borders this time.

Start by clicking the line that’s outlined in red in the screenshot above. Then click anywhere along where I’ve drawn the blue line on the screenshot.  This will create dashed borders between rows.  As a final step, let’s highlight the first row in the table and click the bold icon (That’s the Dark “B” in the Font section on the Home Tab.

If you’re dealing with a very big table, and you want to do more to define the different rows, Excel allows you to format a range as a table.  There are some negatives to doing this, but it might be useful.  I’ll get into the negatives later, but for starters, let’s talk about how to do this.  Select your entire table of data, and click the Format as Table button in the Styles Section of the Home Tab.  You can see it on the far right of my number formatting screenshot. When you click that button, it will give you options of the table styles available.  Let’s pick the green one in the middle.



You’ll get a box asking where the data for your table is, this will be your current selection by default, and since our data does have headers, let’s leave that checkbox checked, and click ok. You’ll notice that our formatting from before is still there, but now we’ve got colors indicating where the various rows are, and highlighting the header row.  You also see drop down buttons that allow you to filter your table based on the data in the given columns.

Now for the caveat.  These tables don’t work the same as other data in excel.  Using a table makes your data more readable, but it can make your formulas less readable. Suddenly, your cell references become table references. So it references the data based on where it is in your table, rather than the cell within the spreadsheet.  It can make your life difficult. My rule of thumb is, if I’m going to be using the data for other formulas or reports, I avoid the table.  If I’m planning on having the table itself be the report, then I will consider formatting it as a table to make it easier to read.  Use this feature wisely, and you’ll avoid a lot of frustration.

Well, that’s the end of my lesson for today.  There are a lot more things you can do with formatting than what I’ve shown, so get in there and start playing around, learn all the great ways you can format your data for maximum readability.