Formatting Data in Microsoft Excel

Format Data as a Table

When you enter data into Microsoft Excel, you enter it into a "range" of cells. Those ranges can be referenced for more sophisticated work, but for most Users, the range is just where the data starts and where the data ends. If you have a "Customer" table with the columns, Name, Phone Number, and Shipping Location and the table had 4 records, the range would begin at the cell that the Name column header is and end in the Shipping Location of the last record.

You can format the range as a table and then name the table "Customer." Again, most Users will not need to reference the table - and if you did, you could reference the Customer table in your future formulas. The added benefit is that when a new record is added, the Customer table reference will automatically update to include that and any other additional records. When you use a range, the reference must be updated with each additional record!

Working with Column Widths

Microsoft Excel, like all popular spreadsheet programs, organize data in Rows and Columns. Rows are the horizontal spaces that typically are numbered and Columns are the vertical spaces that have letters at the top of the columns. Where a row and a column intersect is where a specific piece of data is located, in the cell.

Cells are as wide as the column and as tall as the row is at the intersection. Data will act differently depending on how the cell is formatted, so it's important to understand early what your data will look like under different circumstances and how you can adjust the properties of rows, columns, and cells so that you're getting the information you expect.

A common encounter for Users is seeing data that doesn't seem accurate because they have a column width that does not fit the number of characters in a piece of data. When numbers are concerned, there might be several hashtags (#) showing instead of the digits that make up the number. Otherwise, many users get shortened phrases or words if all the characters of a piece of text (like a name) do not fit in the column width.

To change a column width, you can use the cursor to drag the right edge of the column to a desired width. You can double click on the right edge of a column and the width with automatically adjust to fit the cell in that column that is longest. This will ensure that all values in the column will fit, since every other length will be less than the longest and is the usual approach. A third approach is to right click on the column and adjust the column width manually. This is most effective when you have strict formatting rules and is probably the least common approach for most Users.

A final note on column widths: Text will "overflow" into the columns that are to the right of it when there is no data in those columns by default. This is a setting that can be changed in Excel's options, but it is out of the scope of this topic for now. Once you enter data into the cell next to an overflowed cell, the text will be cut off as described in this section.

Conditional Formatting

Users typically make data look a certain way so that it is more readable - a powerful and less used tool in Excel is Conditional Formatting. This is not a necessary feature, but it does make reading data much easier when you have specific indicators to find. Rather than sorting a list to find all of the customers who have had sales over $10,000 for the month in a marketing campaign to thank valued customers, you can program Excel to automatically highlight these customers.

Conditional Formatting can be found in the Home tab at the top of the Excel screen. The dropdown shows several ways to set this up with preset formatting - or you can create your own!