≡ Menu

Excel

Excel’s Missing RANKIF Function

Excel’s Missing RANKIF Function Thumbnail

Excel offers a number of conditional IF functions such as COUNTIF and SUMIF which allow you to count or sum a range based on the values in another column. If you have Excel 2007 or later you’ll have the really useful COUNTIFS and SUMIFS functions which allow you to perform multi-condition actions based on the [...]

{ 3 comments }

Revealing Formulas in Hidden and Protected Cells

Revealing Hidden Cells

Chandoo published a useful tip recently for revealing the contents of hidden cells. Anyone who works in the NHS and ever has to deal with excel spreadsheets that originate for the DH will be very familiar with the frustration of working with workbooks that have been protected to the max. I’ve been working on the [...]

{ 0 comments }
Massive Excel Spreadsheets – How to Reduce the Size of Pivot Table Workbooks Thumbnail

Since we’ve moved to excel 2010 with its expanded data range the tendency has been to create ever more detailed reports. Where once we summarised data we can now leave in every last bit of detail. Detailed reports always need summaries which is where the pivot table comes in handy. So now we have huge [...]

{ 2 comments }

There is a division in the office between the have's and the have nots. Those that have taken the brave leap into the unknown and upgraded to Excel 2007 (or even Excel 2010) and those that are resolutely sticking to the old stalwart, Excel 2003. It's a nuisance. Perfectly decent spreadsheets are incapacitated when you [...]

{ 3 comments }

How to Use Excel VBA to Log-In to a Website

How to Use Excel VBA to Log-In to a Website Thumbnail

I've been tasked with downloading regular data updates from a national reporting website but as this website has user log-in functionality. This means I have to set the macro up to open the webpage and then automatically enter the user ID and password. I found a very useful tutorial on accessing google webpages via VBA, [...]

{ 2 comments }

Macro to Insert a “Last Update On:” Date Value

Macro to Insert a “Last Update On:” Date Value Thumbnail

I have a number of summary reports that are updated by the use of a macro update button. I usually include an area on the report to include a datestamp that indicates when the report was last updated or refreshed. This is the code I use: [cc lang="vb"]Dim VERSIONDATE As String VERSIONDATE = Format(Now, "dd-mm-yy [...]

{ 0 comments }
Macro to Consolidate Data Ranges from Multiple Excel Spreadsheets Thumbnail

I have a lot of macro workbooks that consolidate multiple data ranges from individual tabs in the same workbook into one summary tab but today I discovered how to consolidate data ranges from different excel workbooks into one summary workbook. My task today was to consolidate one of the worst NHS SLAM reports that we [...]

{ 2 comments }

The SUMIF Function using Named Ranges

The SUMIF Function using Named Ranges Thumbnail

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

{ 1 comment }

Waterfall Charts that Cross the X-axis

Waterfall Charts that Cross the X-axis Thumbnail

Waterfall charts have almost reached cult status in the office recently. They are popping up all over. We are well into the new years planning process and a waterfall chart is the perfect way to demonstrate how a starting position either increases or decreases through a series of changes. So in our case, when planning [...]

{ 1 comment }

How to Test for an Integer Value in Excel

How to Test for an Integer Value in Excel Thumbnail

I was looking around today for an ISINTEGER logic function in Excel but I can save you the bother and tell you it doesn’t exist. I picked up fractional activity counts in our reported YTD position and wanted to quickly highlight which lines were affected so that I could assess the problem. You may expect [...]

{ 1 comment }