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 [...]

Excel Tutorial

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 [...]

Excel Tutorial

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 [...]

Excel Tutorial

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 [...]

Excel

What no Status Bar? Thumbnail

What no Status Bar?

The status bar is a jolly handy part of the excel spreadsheet window. I'm sure you use it all the time and would miss it terribly if it disappeared. Well I do and it did! It's easy enough to get back provided you know what its called but how many of you can point to [...]

Excel

Values Stored as Text Thumbnail

Values Stored as Text

One of my work colleagues is driving me nuts! On an almost daily basis he drags me over to his desk to correct the exact same problem, he thinks his VLOOKUP isn't working properly but it is always the case, that in one sheet, his data is stored as text. I can see the problem [...]

Excel Tutorial