Because Excel is used to visually explore datasets and present those datasets for others, we often want to change the way tables are displayed to improve human readability.
We’ve loaded a table of data in the first slide in the learning environment. This table is perfectly accurate, but it isn’t very easily readable. We want to present tables that provide insight, not tables that provide headaches!
We can solve this problem using Excel’s formatting tools. These tools change the way data is displayed without changing any of the underlying data to maintain data integrity. Play through the slideshow in the learning environment to see how we can reformat this data using Excel. What do you think of the end result?
We’ve emphasized that formatting doesn’t change the value of a cell. Let’s look at an example of why this is important to remember.
In the Excel table below, the cell B3 is adding the values in B1 and B2.
| A | B | |
|---|---|---|
| 1 | 1.1 | |
| 2 | 1.4 | |
| 3 | sum: | 2.5 |
If we format these numbers to have no decimal places, the table will be displayed like this:
| A | B | |
|---|---|---|
| 1 | 1 | |
| 2 | 1 | |
| 3 | sum: | 3 |
It seems as if Excel is calculating 1+1=3! But this is just a consequence of the rounding, the underlying summation is still 1.1+1.4 = 2.5.
When you’re ready to practice formatting for yourself, get started on the next set of instructions!
Instructions
When you’re ready to practice what you’ve learned, download and work through our interactive exercise spreadsheet. Note that our solutions are contained in the same file for this exercise!
Reflection question: what would happen if a cell was formatted as a number and then you type a date into it?