How to Highlight an Entire Row in Excel with Conditional Formatting

Whilst changing the colour of single cells in a spreadsheet using conditional formatting is straightforward, how do we highlight an entire row?

Well it’s actually quite straightforward, we just use a simple formula to achieve the desired effect. Here’s how to do it in 3 east steps!

In the following training example, we want to identify any invoices which have exceeded our 30 day payment terms. You can download the spreadsheet Highlighting Rows here.

STEP 1: First select the range of cells to be formatted, then click the Conditional Formatting button and choose New Rule.

STEP 2: Now select Use a formula to determine which cells to format and enter the following: =$F4>30

STEP 3: Next click the Format button and choose an appropriate fill colour, then click OK – that’s it!

So how does it work? Well, notice the $ in front of the reference to the column in $F4? This tells Excel that it should hunt down the whole of column F looking for values that are greater than (>) 30. However, the dollar effectively ‘locks’ the column, preventing Excel from looking to the left or right. Because we selected the whole table of data, Excel will apply the conditional formatting to the entire row.

I hope you’ve find this short Excel tutorial useful and if so, a share would be greatly appreciated. And do please leave your feedback!

Learn How to Password Protect Selected Cells in Excel in Under 5 Minutes!

Sharing spreadsheets with colleagues can sometimes lead to important formulas being accidentally overwritten. Locking the workbook is one solution, but this means that no one can work on any part of spreadsheet without the password.

A better solution might be to selectively lock your spreadsheet, leaving some cells open and editable without a password. Sounds complicated right? In fact, it’s one of the simplest things you can do in Excel!

Step 1: First we need to select the cell or range of cells which we DON’T want to be protected. In the example below we chose a range of cells which all contained formulas. This is perhaps counter-intuitive, but you’ll see why in a moment!

Step 2: Next we right-click the mouse and choose Format Cells, then select the Protection tab. You will see two tick boxes marked Locked & Hidden.

Step 3: Now untick the box marked Locked and then click Close.

Step 4: Next select the Review tab of the ribbon menu and click the Protect Sheet button.

Step 5: Enter a password, click OK and then confirm your password.

By default, all cells in Excel are marked as protected. This means that when we apply a password, the entire worksheet is locked. What we have done is tell Excel that there are some cells which we don’t want to protect – simple!

I hope this is might save you the hassle of repairing damaged formulas and data. If you find it useful, do please share with your colleagues!

How to Use Excel’s Handy SUBTOTAL Function

The SUBTOTAL function in Excel allows us to work easily with lists and databases which have been filtered, or perhaps contain hidden rows.

In the following example we’re using a spreadsheet called ‘Car Sales database – Subtotals’ which you can download here.

You can see that we have already totalled the number of sales in cell B1, which comes to 166. However, if we then filter the data to show only Claire’s sales, we still get a value of 166.

We need to be able to calculate the total for only the visible data. This is where the SUBTOTAL function comes in!

SUBTOTAL is actually a number of functions in one. For instance, we can sum, average, count and find minimum and maximum values, all with the same basic function.

The function itself contains two fields (or arguments): Function Num and Ref. The function number determines what you want the function to do and Ref is simply the range of cells you wish to apply it to.

Here is a list of the most popular uses for SUBTOTAL and the corresponding function numbers:

  • SUM: 109
  • AVERAGE: 101
  • MIN: 105
  • MAX: 104
  • COUNT: 102
  • COUNTA: 103

Let’s look at an example of how this works, using the SUM function:

Step 1: click in the cell where you want to place the formula (in this case cell B2), then click the Insert Function tool button (fx)

Step 2: Now choose the SUBTOTAL function (you might have to use the search box) and click the OK button

Step 3: We are totalling data in this example, so we need to type 109 in the Function Num field

Step 4: Next we select the full range of sales in the database (C5:C160) and click OK.

Initially our subtotal sales will be the same as the total sales (166). However, if we now filter for Claire’s sales, we can see that she sold 23 cars.

Using the various Function Number options allows us to analyse our data in different ways, however the above steps are exactly the same.

One final point: as we’ve seen, SUBTOTAL automatically omits data that has been filtered. However, we have a choice to omit or include data which has been manually hidden. Using the previous Function Number table, manually hidden data is excluded. If you wish to include manually hidden rows, just use the following Function Number table instead:

  • SUM: 9
  • AVERAGE: 1
  • MIN: 5
  • MAX: 4
  • COUNT: 2
  • COUNTA: 3

I hope you’ve found this short tutorial useful and if so, a share would be really appreciated!

Easy Excel Functions in 4 Steps!

Functions expand the scope of Excel and allow us to perform tasks which formulas alone cannot do.

There are two separate methods for entering functions. In one method we use an interface (the Insert Function tool) and in the other we simply type the function directly into a cell.

In the following example we’re using a spreadsheet called ‘Using Functions’ which you can download here.

Let’s take a look at the Insert Function tool first:

Step 1: click in the cell where you want the function to be placed, the click the Insert Function tool button (fx)

Step 2: Choose a function from the recent list (entitled Select a Function), or use the search box at the top of the window.

Step 3: Now click the OK button – the Insert Function window will close and the Function Arguments window will open

Step 4: Select the range of cells you want to apply the function too and then press OK – and that’s it!

These are the correct results if you want to check your work:

SUM: adds cell ranges

MIN: returns the smallest value in a range

MAX: returns the largest value

AVERAGE: average value across the range

COUNT: returns the number of values in the range (numbers)

COUNTA: counts both values and text (if present)

If you have multiple ranges of cells, you can make use of the additional argument fields (Number 2, Number 3 etc). Simply click in the Number 2 field and select an additional cell range. You will now be presented with the Number 3 field in case you have further data to add. This will happen automatically and actually allows us to add up to 255 separate cell ranges!

Now let’s take a look at the second method:

Step 1: Click in the cell where you want to place the function, then press the = key.

Step 2: Now start typing the name of the function – you will be presented with a menu which provides a simple shortcut to save typing the whole name (note you can ignore this and just manually type the whole function)

Step 3: Next select the range of cells you wish to apply the function to – press Enter and you will see the result! Note that you don’t need to type the closing bracket, Excel will do this automatically with a single function.

To add additional arguments using the method, just type a comma to separate each. Again, we can add up to 255 additional cell ranges.

And that’s all there is to it! I hope this short tutorial has shown you how easy functions can be in Excel. There are around 400 different functions available and they all follow these same basic principles.

If you’ve found this useful, please share with your colleagues and visit us again soon for more easy Excel tutorials!

Master Excel Formulas in Under 5 Minutes!

If you’ve never set up a formula in Excel before, you might think that it would be difficult. Well think again! In this quick tutorial I’ll show you how to master Excel formulas in 4 simple steps in under 5 minutes.

Microsoft Excel works in a similar way to a calculator. If I asked you if you could add 2+2 on a calculator, you would say “of course, that’s easy!”

The difference with Excel however is that we don’t add numbers, we add cell references instead.

In the following example we’re using a spreadsheet called ‘Simple Formulas’ which you can download here.

Step 1: click in the cell where you want to place the formula (in this case C1) and press =

Step 2: next click in the first cell that you want to add (A1)

Step 3: now type + and click in the next cell (B1)

Step 4: press the Enter key and the values in both cells will be added together. The result of course will be 4 – simple!

Subtracting, multiplying and dividing all work the same way. In the next example, we’ll subtract cell B1 from A1:

Our result this time should be 0!

Multiplication works in the same way, however the symbol that we use is *. You can use the number pad on your keyboard, or simply press shift+8:

Now our result will be 4.

Finally, division uses the / symbol and so our formula looks like this:

2 divided by 2 will give you 1.

And that’s it, I did say formulas in Excel are easy! Of course some formulas will be longer and more complex, but the same basic principles always apply. As a reminder, the arithmetic symbols that we use are:

Addition: +

Subtraction: –

Multiplication: *

Division: /

I hope this short tutorial has been useful and if so, please share with your colleagues who might also find it beneficial.

This is the first in a series of a new Microsoft Office tutorials, so check back weekly for more tips and tricks!