Summary: The purpose of this KB article is to provide instructions for formatting Microsoft Excel worksheets
Audience: NUSD staff
Source: Geoff Lilley Excel Training Manuals
*Note: these specific instructions apply to Microsoft Excel on a PC Computer. If you are using a MAC, the ideas/concepts are the same, but the placement/titles of the buttons will vary slightly.
Excel offers a range of options to change the appearance of your data, including:
- Altering the cell background color
- Modifying the border around the cell
- Formatting a cell conditionally (formatting only cells that meet a certain condition)
Activity 1.–Insert a Row
- Click cell A2
- Right-click and select Insert > Shift Cells Down > Ok.
Activity 2.–Add Borders to Cells
Borders are one of the many ways to help make data stand out in Excel. Imagine borders as a way to draw lines around the cells, so that they’re easier to read.
- Select any group of cells that you’d like
- Click the Borders button
You’ll be presented with a variety of options. Generally speaking, the best way to make your data stand out is to do a thin border between each individual cell, and a thick border around edges of the data
Activity 3.–Use Conditional Formatting
When you have a large worksheet of values, you may want to spot the unusual values right away. Use Conditional Formatting to help you find anomalies in your data.
- Fill a small portion of a new excel worksheet with numbers between 1 and 10 (only put one number in each cell)
- Select all of the cells with numerical values in them (Do it the easy way. SHIFT+CTRL+DOWN to select to the last row, then use SHIFT+RIGHT ARROW to select just a few columns.)
- On the Home tab, select Conditional Formatting, and then choose New Rule
- Select Format only cells that contain
- From the third menu, select Cell value, and then greater than Cell value, and then greater than
- Enter the number 6
- Click Format
- Click Fill
- Choose a green color
- Click OK, and then click OK
You will notice that all numbers in your data set that are greater than 6 are now highlighted green. To add a red color to low numbers is pretty similar; since you still have the cells selected, that’s one less step to worry about.
- Just like before, select Conditional Formatting, and then choose New Rule
- Select Format only cells that contain
- From the third many, select Cell value, and then Less than Cell value, and then Less than
- Enter the number 5
- Click Format
- Click Fill
- Choose a red color
- Click OK, and then click OK
You will notice that all numbers in your data set that are less than 5 are now highlighted in red. If you change the value of a cell with the number 3 (highlighted in red) to the number 8, it will automatically highlight that cell in green.