I was recently asked about formatting cells in Excel, specifically about changing the colour of the cells. This made me think that perhaps it’s time to write a blog post about cell formatting, so here goes.
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, all the things in the Font Group on the Home Ribbon.
Now 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 on.
To change the formatting, select the text or numbers to change and then make the change from 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 colour choose the colour you want from the drop down list under the A and to add borders, choose the border style you want under the borders icon.
I have written about number formats in a previous post you can find it here – 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 need to use the right format so that Excel knows that it can work with that number.
When you add numbers to the cell they start off with a General Format. Now 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 add 30 days to a date to find when an invoice is due for example, then it is very important to tell Excel that this number is in fact a date.
There is no need to type in notation 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 then 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 I have a date?
When you type in a date you can type it in several ways.
3rd July 2019
3 July 2019
All of these will change the number format to show Date. However, if you type in 3.7.2019 then Excel will not know that this is a date and will treat it as text. It is made up of numbers and full stops. None of the symbols that Excel will use to recognise a date format are present. If you try and 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 and apply the Short Date or Long Date format to them and then as you type in the date it should show the format you have selected.
Now for the question I was 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 where you can pick from a larger selection or 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) now make your selections (4)
Here you can choose to add a pattern style and also choose from Fill Effects.
Once you have made your selections, the cell colours will have changed.
Now that you know how to change the Cell Formatting in three of the ways, look out for another post from me, which will look at Borders, Alignment and Cell Styles in Excel.
Leave me a comment and let me know how this has helped.
To learn more about Microsoft Excel, take one of my Excel Courses – there is one for Windows and one for Mac and more are in the pipeline.
Visit – www.tomorrwosva.com/courses and go get :
Essential Excel Skills for Business the Windows Version
Essential Excel Skill for Business the Mac Version
And of course there are lots of other lovely courses to choose from!