Instead of tweaking individual cells in your data set, make adjustments to a whole table, row or column. Which spreadsheet would you prefer to work with?

à

This post will help you get from Table 1’s version of your data to Table 2’s version. To do so, we’ll be taking a closer look at some of the options in the Font, Alignment, Number and Style sections on the Home tab of the Ribbon.

Let’s go.

Step 1: Set the Table Format

1. Highlight all the cells in the table by clicking in the top left cell (A1), then hold down SHIFT and click on the bottom right-most cell in the table (in this case, H)
2. On the Home tab of the Ribbon, click on Format as Table.
3. From the dropdown menu, click on Table Style Light 9.
4. From the Format As Table window, click on the box next to My table has headers to add a check next to it, then click OK.

Note: Notice that in the Header Row, the filters are turned on for each column. This allows for filtering by data values in each column.

Step 2: Format the Header Row

1. Right-click on the row header to select the whole row you want to highlight (Row 1).
2. From the drop-down menu, select Format Cells.
3. In the Alignment tab of the Format Cells window, add a check to the box next to Wrap text by clicking on it.
4. Then click OK.

Now, if the text in the Header Row gets longer than the width of the column, the row’s width will increase to fit the text.

Step 3: Adjust Column Widths

1. To make a row’s width as wide as the field with the longest text in the column, hover over the boundary of the column (Column A) and the column to its right (Column B) until the cursor changes shape. Then double left-click.

     à     

2. To make all or certain columns the same width, select all columns (Columns B – H) by left-clicking on Column B and holding it down until all the columns between B and H are highlighted.
3. Then click at the top on a selected column boundary and drag it to the desired width. The width below is 10.00.

Step 4: Add Leading Zeros to a Column Using a Custom Numbers Format

Have you ever noticed that if you type a zero at the beginning of a chain of numbers in a cell and hit enter, that zero typically disappears? This can be especially annoying if you are trying to type in a zipcode or ID number that is supposed to have a set amount of numbers but starts with zero. That’s where leading zeros come in. Leading zeros can help keep those zeros around.

1. Right-click on the column header (Column C) to highlight the entire column.
2. From the drop-down menu, select Format Cells.
3. In the Number tab of the Format Cells window, select Custom from the Category list.
4. In the Type field, add five zeros. This will make all the fields in Column C have values of five numbers long, adding leading zeros when required.
5. Then click OK.

Learn more about using custom number formats to display leading zeros in this article.

Step 5: Formatting Dates

Multiple date formats in a column can be confusing. Let’s get them all into the same format.

1. From Step 4, follow #1 and #2 to open the Format Cells window. Instead of highlighting Column C, highlight Column H, which has the dates we want to update.
2. In the Number tab of the Format Cells window, select Date from the Category list.
3. In the list under the Type, click on 03/14/12.
4. Then click OK.

Step 6: Formatting Percentages

When our data is clean and well-formatted, it is easier to read and absorb the information quickly. Formatting does not have to take long. Here are a few quick tips.

1. From Step 4, follow #1 and #2 to open the Format Cells window. Instead of highlighting Column C, highlight Column G, which has the numbers we want to convert to percentages.
2. In the Number tab of the Format Cells window, select Percentage from the Category list.
3. Change the Decimal places field to zero.
4. Then click OK.

Numbers can also be changed to Percentages from the Numbers Section of the Home tab of the Ribbon by clicking on the % icon.

Below is an image of how our table looks now, after formatting our table, header row, and column widths. We also adjusted the format of leading zeros in Column C, dates in Column H and percentages in Column G.

Bonus Step: Adding Conditional Formatting

Conditional Formatting is a topic we have covered before. If you’d like more information about how to highlight duplicates in the same column, color scaling and use simple equations in formatting, read this previous blog post.

We hope this article helps you love your data a little more!

* * * * *

Are you interested in learning more Excel tips and running reports? Check out other articles on the HMIS blog.

Want to sign up for our newsletter? Go to the HMIS website, scroll down until you see the ‘HMIS E-mail Updates’ heading in the right column, add your email address, then click ‘Submit.’