As an early end of year present, I have released version 1.3 of the Excel Colour (color) Palette Changer, which is no longer time limited. It allows you to easily change the color palette in an Excel workbook. Note that it is specifically for use with Excel versions 97 through to 2003. Excel 2007 uses …

I know many people continue to struggle with finding commands on the new Excel 2007 Ribbon. There are also numerous add-ins available that will provide a 2003 style toolbar on the 2007 Ribbon. If this is you, and you need the reassurance of having the 2003 toolbar available while you are getting familiar with the …

If you have a value appearing in the same cell in multiple worksheets, then you can summarise these values in a separate worksheet. For example: =SUM(Sheet1:Sheet5!B11) – will add all the values that appear in cell B11 on all worksheets from Sheet1 through to Sheet5

Today I have released version 1.2 of the Excel Colour (color) Palette Changer. This version provides more detailed descriptions of the specific uses of the colours defined by the Excel Color Palette. You can find the update on the downloads page.

As a general rule, avoid using Merged Cells in your Excel spreadsheets. Although it can aid in the appearance of a worksheet, it can also be the cause of numerous problems. Some problems that Merged Cells cause include: – the paste function of a selection that contains Merged Cells is limited and can be inconsistent; …

There are instances when you have a series of cells that contain formulas and you need to add a fixed percentage to them all. There are some text tools available that will allow you to add text to the beginning or end of some text in a cell, but when it comes to doing the …

Select the range that you want to extract the unique values from; Select the Data menu, Filter, Advanced Filter, Copy to Location, Unique Values: Unique values are copied from the selected list to the location you specified.

While named ranges are good, dynamic named ranges are great! Making a named range dynamic, becomes a really powerful feature. To do this, means changing the formula that the name points to. Instead of a static range of a single cell or range of cells such as: … it is possible to name a range …

Getting rid of external links in a workbook can be a real headache. They show up when you attempt to save a file. An example of saving a template with external links produces the following message: “This workbook contains external data.  Do you want Microsoft Excel to clear the data before saving the template, and …

One of the really powerful features of Excel is the use of named ranges. Use the Name Box to add clarity to your formulas: To name a cell, select it and then type a name for it in the named box. In the example below, the cell A2 has been named “Area1Value”. Then when you …