Sum Every Nth Row in a List

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

Continue reading

Create a Histogram in Excel

A Histogram is used in statistics to graphically represent the distribution of data. It looks like a column chart with each column representing an interval (bin), and the column height representing the frequency that it appears.

Essentially the graph groups numbers into intervals (bins) and displays how often they appear. The graph then beautifully illustrates how the sets of numbers are distributed.

Continue reading

Sum a Column Ignoring Formula Errors

Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.

If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as ISERROR and IFERROR.

However, if your spreadsheet is large, having these IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.

There is a function in Excel called AGGREGATE which allows us to perform various functions on a range whilst ignoring formula errors.

Continue reading

Learning Excel – 5 Awesome Date Functions in Excel

Do you want to advance your skills by learning Excel? Performing date calculations can sometimes be troublesome. Trying to calculate the number of working days between two dates, or automatically finding the date in two months’ time is not as straight forward as formulas with general numbering can be.

Fortunately Excel has a full repertoire of fantastic date functions. Here are five of the best.

Continue reading

Insert a Picture into a Comment

Excel was created for the use of calculating and analysing huge amounts of data. Because of this it has never been great for storing large amounts of text or pictures. Inserting images into a cell would make the spreadsheet even larger and more awkward in its daily use.

Comments are fantastic for leaving notes for other users, but they can also be used to store pictures. These pictures can then be made visible when required rather than permanently displayed on screen within a cell.

Continue reading

Excel Badminton League Table

This Excel badminton league table can be used to track team or player positions real time as results are entered into the spreadsheet.

Download the Excel badminton league table.

This spreadsheet uses a few excellent Excel techniques for its functionality. These include;

To use the spreadsheet. Simply add the team or player names to the table on the Teams sheet and fixtures sheet (Find and Replace helps here), and then enter the results into the fixtures sheet.

Excel Gantt Chart Template for Tracking Project Tasks

A Gantt chart is used to plan and track the progress of a project. Although Excel does not contain a Gantt chart feature (maybe one day), its tabular structure and wealth of tools provide us with the means of creating one.

A Gantt chart can be created in many ways to match your requirements.

Using the Excel Gantt Chart Template

This Excel Gantt chart template uses fixed scheduling on its tasks and provides a timescale of 1 full year from the project start date. To use the template;

  1. Enter the project start date in cell E1.
  2. Enter the ID and name for the tasks of your project.
  3. Enter the task’s estimate start dates and durations.
  4. Enter the % completion to update the chart with the progress of the project.

Continue reading

Spell Check a Protected Excel Worksheet

Worksheet protection is used in Excel to protect cells containing formulas, hide sensitive data and much more.

Unfortunately one of the problems that arises from protecting a worksheet is the inability to spell check a worksheet.

To be able to spell check a protected worksheet you need a macro to unprotect the sheet, perform the spell check, and then protect the sheet again. Continue reading

Christmas Movie Quiz Spreadsheet – Test your Knowledge

Think you know your Christmas movies? Why not try this Christmas movie quiz.

There are 20 movies to recognise from a picture. Select the film from the list below the picture. A running total is kept at the top of the sheet.

Download the Christmas movie quiz spreadsheet.

Continue reading