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 on the Basic Excel 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 checklist for you to question how much you know about cell formatting, just some Excel hints on the topic from us!

  1. Format Painter (FP) button

Excel hints - Format Painter Icon

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.

  1. Conditional Formatting

Excel hints - Conditional Formatting on AgeThis 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’. See the example below. 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 Excel Master Class course

Excel hints - Creating a Rule for Conditional Formatting

  1. 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.

Excel hints - Date Formatting Screen shot

  1. Quick Way to make a cell pounds and pence

Excel hints - Accounting Formatting ButtonThis button is on the Home Tab in the middle of the number gallery.

  1. 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.

Excel hints - Formatting with Lock Cells

We hope you find these tips useful and can apply them to your work in Excel!

Like these Excel hints? Feel free to leave us a comment by email on johnlegge@jplcomputer.co.uk

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy