Excel

Vlookup Errors – The Third Way

I've just published a post detailing a method of removing the errors returned by vlookup or hlookup utilising conditional formatting. I had previously been using the double vlookup method to replace errors with blanks or zeros but then discovered that these excessive lookup functions had a tendency to bloat my spreadsheets. While the conditional format [...]

Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors Thumbnail

Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors

I’ve started working at a new hospital and it seems to be plagued by unwieldy spreadsheets that have a tendency to crawl through saves and then sometimes refuse to open. They are riddled with links and lookups to external spreadsheets and the whole thing feels fairly precarious. I’ve been reading a bit about good spreadsheet [...]

Adding an Image to the Header or Footer of Every Sheet

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook. [cc lang="vb"]Sub MultipleHeader() 'created 10/01/2008 by Angela Wolff Application.ScreenUpdating = False 'stops the [...]

Scroll Bar Chart Thumbnail

Scroll Bar Chart

This is a very neat advanced Microsoft Excel technique that enables the spreadsheet user to alter the visible range displayed. This can be achieved by defining the start point within the spreadsheet data series and also the number of data points to display in the Excel spreadsheet chart. All the examples are available to examine [...]

Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet. Here we start with monthly data for Retailer A, showing Petcare sales complete up to July. A standard Excel line chart of this data would be shown thus: To [...]

Sumproduct

All the worksheets (and some extras) shown in the microsoft excel tutorial below are included in the example spreadsheet, available to download Sumproduct.xls This is quite a special excel spreadsheet function but the online help available from microsoft is unfortunately pretty poor. It works as an advanced form of the SUMIF formula where you can [...]

Named Ranges and the SUMIF Function

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name: areas of the spreadsheet, such as your data columns and rows or individual cells that may act as constants, for example the VAT value I've used the following named ranges in the SUMIF example below: As usual [...]

Sub Totals and Pivot Table Reports Thumbnail

Sub Totals and Pivot Table Reports

Removing sub totals from pivot table reports When you add detail to pivot table rows, Excel will automatically add a subtotal row. This can very quickly litter your pivot table with unnecessary detail. I remove the unwanted subtotals by right clicking on one of the totals and selecting "Hide". Adding sub totals to pivot table [...]

Colourful Spreadsheets Thumbnail

Colourful Spreadsheets

If you want to produce Excel reports with impact, you have to consider formatting as well as data content. One of the flaws of Excel is the rather garish colour palette provided as standard, which seems to favour the production of colour clashing disasters. Although it is possible to choose custom colours when formatting regions [...]

Features test

The Postie program has much to recommend it, there seems to be a heck of a lot of control features enabling you to post fairly nifty blog messages via email. Not having a lot of luck getting Cronless to work but I'm hoping I can trigger the wp-mail.php from my mobile which would suffice as [...]