Excel Hints and Tips – Formatting in Excel
“We can all format in Excel can’t we?”
When we deliver courses on Excel which we do every week they quite naturally focus on formulas and logical data analysis tasks. We do train this on the Basic Excel training course cell formatting but it’s a smaller section as a lot of folk seem to know it but do they really….
Here’s a list of features for you to question how much you know about cell formatting, just some Excel hints on the topic from us!
Format Painter (FP) button
This wonderful button allows you to paste the cell format from the source cell to destination cells. If you first click on a cell which is already formatted and then single click on FP then on to a cell unformatted it pastes the format across to the new cell. Double click on FP and you get multiple chances to paste across cells. Escape key switches off FP.
Conditional Formatting
This feature allows you to create a number of rules for selected cells. So if you have a range of numbers, you can apply colour to them based upon some criteria. In the example to the left, if the age is less than 30 its pink else its blue. These can be edited by using Manage Rules. There are even options to use data bars and icon sets to illustrate your data.
An extension to this feature Is to use ‘conditional formatting with formulas’ . To do this you take the option to ‘use a formula to determine which cells to format’. This would fill colour all the rows where there is a letter L in column H (Formula: =$h2=”L”). More examples of this are on our Master Class Excel Bronze course. See the example below.
Date formatting as a day
This simple technique again covered in our Basic Excel course converts the format of a date cell to a day. See example below.
Quick Way to make a cell pounds and pence
This button is on the Home Tab in the middle of the number gallery.
Cell formatting with Locked Cells and Protection.
It is possible and useful to lock and protect cells which contain formulas. To do this you must unlock everything, choose what to lock (e.g. formulas cells) and then put a password on the sheet. (Review Tab). In the simple example below cell A4 has been locked whilst A1:A3 are unlocked. Cell A4 has a password, so in effect no one can change the formula unless they have the password. This is covered in more detail on our Intermediate Excel course.
We hope you find these tips useful and can apply them to your work in Excel!
Like these Excel hints? Why not take a look at our previous post on some features in Excel?