Perhaps you think of Excel as just a list of numbers – I know I did when I first started using it. And yes, of course it is a useful tool for presenting data, but that doesn’t mean we can’t add some style and interest to our spreadsheets using formatting.
Good formatting makes the data in a spreadsheet stand out as the type of data it is. Differentiating between, for example, general numbers, currency and dates brings meaning to your worksheets and makes them easier to understand. What’s more, applying the correct formatting to a number means you can carry out calculations, which is after all what Excel is best at.
Formatting does several things:
- Makes certain things stand out
- Makes your spreadsheet easier to read
- Makes things neat and tidy
What can I format?
- Font style
- Number format
- Cell shading
- Text or Cell Colour
- Column width
In today’s post I will look at the top three on the list and come back to the rest in another post.
This is the font face, the size, the alignment – in other words, all the options in the Font Group on the Home Ribbon.
You can also create Styles in Excel just like In Word – well almost like in Word. The principle is the same. More on that later.
To change the formatting, select the text or numbers to change and then make the change using the icons in the Font group. You can see that my font is Arial and 12 points in size with nothing else added.
To change the font colour, click the drop down arrow on the A icon and choose the colour you want. Or to add borders, choose the border style you want from the Borders icon.
I have written about number formats in a previous post – Why Number Formats Matter In Excel. However, it is so important to get the number format right that it warrants repeating (hop over and read the Why Number Formats Matter in Excel blog anyway!).
When you add a number to a cell and you want to use that number in a calculation, whether it is a simple number or a date, you must use the right format so that Excel knows that it can work with that number.
When you add numbers to the cell, the General format is applied. Excel will be able to do most things even with a General format, but if you have entered a date and want to work out the difference between two dates, or for example add 30 days to a date to find when an invoice is due, then it is very important to tell Excel that this number is in fact a date.
There is no need to type in notations such as Currency symbols, these can all be controlled by the number format. This is a great time saver, as you can just type in the numbers and apply the formatting later. Or apply the formatting to the cells first and then, as you type, the notation is applied.
How do I tell Excel that I am entering a date?
When you type in a date you can type it in several ways.
3rd July 2022
3 July 2022
Typing a date in any of these ways will change the number format to a Date. However, if you type in 3.7.2021, Excel will not see this is a date and will treat it as text because it is made up of numbers and full stops. None of the symbols Excel uses to recognise a date format are present. If you try to add 30 days to this number, it just won’t work and you will get an error message. You can, of course, select the cells first and apply the Short Date or Long Date format; then as you type in the date it should show the format you have selected.
Now for a question I am often asked: How do you change the background colour of a cell in Excel?
There are a couple of ways to do this.
Select the cells to change then click on the drop down under the Fill Colour icon and choose the colour to apply.
You can choose from the Theme colours or click More Colours to pick from a larger selection or to add the RGB values for a specific colour.
Alternatively, select the cells to change (1)
Click the drop down under the Borders Icon (2)
Then click on More Borders (3) and make your selections (4).
You can also choose to add a Pattern Colour or Style or choose a Fill Effect.
Once you have made your selections, the cell colours will have changed.
As you see, just a few clicks of the mouse can make your data look so much more interesting and can help make the data more accessible to anyone looking at it. Have a go, see if you can improve the look of your workbooks.
And look out for another post from me that all about Borders, Alignment and Cell Styles in Excel.
To learn more about Microsoft Excel and how you can speed up routine tasks, take a look at my Microsoft Excel courses for Windows and Mac.
Or take a look at the HUB and have access to ALL my courses including any new ones that are added. Always the best value.