Backwards Compatibility – Create Pivot Tables that Work in Excel 2003

by Dr Ang

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 either save as an Excel 97-2003 workbook or open them in the earlier version. Pivot tables are ruined, formatting goes to pot and any spreadsheet protection you've carefully thought out is ignored.

It's Agreement of Balances time in the NHS at the moment, which means multiple accountants are sharing and working on the same excel spreadsheets. Compatibility issues within the office have caused problems and when you think that the output  needs to be shared with other NHS organisations with their own versions of Excel it becomes vital to understand  how to create a pivot table in Excel 2007 that someone can use in Excel 2003.

Saving a spreadsheet as Excel 97-2003 will not save your pivot tables

If you want your pivot table to work when somebody opens your spreadsheet in an earlier version of Excel you will need to create it in an earlier version of Excel, or at least you will have to force Excel 2007 or 2010 to work in compatibility mode. It is not sufficient to simply save your Excel workbook as an earlier version.

If you open Excel 2007 (or 2010) and create a new spreadsheet you can choose file, save-as, and create an Excel 97-2003 workbook version. If you then create a pivot table in this workbook and re-save you will be rewarded with a compatibility error message, informing you that your carefully constructed pivot table will be useless if you persist on saving it in the antiquated version you've selected.

If you search around the help files or internet for a solution you'll find that you need to create the old version while in compatibility mode. It is not altogether clear how to go out about creating a new spreadsheet in compatibility mode.

How Do You Force Microsoft Excel 2007 or 2010 to Work in Compatibility Mode?

Recognising when Excel 2007 has opened in compatibility mode

If you open an old excel spreadsheet in either Excel 2007 or Excel 2010 the new version will automatically default to compatibility mode.

Compatibility mode is effectively Excel 2003 in disguise and once you've got your version of Excel in this state you can create pivot tables safe in the knowledge that they will work in the earlier version.

You can recognise compatibility mode by the [square brackets] that appear after your file name in the workbook header. If you don't see this, you aren't in compatibility mode so don't bother creating your pivot table yet.

So to open Excel 2007 in compatibility mode you need to open an earlier version of an excel spreadsheet. If you don't have one to hand, do the following:

  1. File – New – Create Blank Workbook
  2. File – Save As – Excel 97-2003 Workbook. I suggest calling it "blank 2003 workbook".
  3. Although you now have a blank Excel 97-2003 workbook you are not yet in compatibility mode.
  4. Close the new workbook
  5. Re-open the blank Excel 97-2003 workbook. Tiresome I know.
  6. You have now forced Excel 2007 to open in compatibility mode!

You can either keep the blank Excel 97-2003 workbook handy for future use or you could save it as a 97-2003 template so that you can easily create new workbooks in compatibility mode.

Post to Twitter Tweet This Post

{ 1 comment }

NHS Ref – Code to Grouper and PbR Tariff on your iPhone

by Dr Ang

The NHS Reference or NHSRef app gave me such joy but it is so niche that I almost missed it.

The The NHS Reference iPhone app has been put together by the NHS Information Centre and is the perfect reference source for NHS professionals from clinicians, clinical coders, informatics analysts and finance staff. NHSRef functions as a user friendly version of the mammoth Code to Grouper spreadsheet that is used to derive payment HRGs from OPCS procedure codes and ICD diagnostic codes but it also appears to include the whole of the PbR Tariff Information spreadsheet.

So to review the NHSRef app, it includes:

Clinical Coding details: ICD-10, OPCS-4.6, A&E

Payment by Results (2011/12): HRG4, Exclusions, Top-Ups, A&E bands

Data Dictionary – Specialty, APC, OPA, A&E

Organisational Data: NHS Providers including MFF, top-eligibility and addresses.

I've found NHSRef to be an invaluable resource, using it to quickly access tariff information for HRGs and outpatient appointments by specialty.

If you want to track through clinical coding of patients from diagnostic and/or procedure codes it is much easier to use this app than open up the clunky code to grouper spreadsheet. I'm not in anyway a coding expert but here's a beginners guide to costing "simple" patient spells.

Beginners Guide to Costing Patient Spells using NHSRef iPhone App

Here's a simple example to demonstrate how you can use the NHSRef app to help you cost up a patient spell.

You need to start with the patients case notes so you can determine the diagnosis and procedures (if any).

I've searched for the relevant coding and entered the ICD-10 and OPCS codes above. I've also shown the procedure hierarchy which is clearly shown in the NHSRef OPCS tables. The hierarchy is important for grouping to spell HRGs. Both the ICD-10 and OPCS codes will group to a HRG but you need to determine the dominant one. The code to grouper user manual includes the following useful flow chart:

So in this case the total excision of a kidney has a hierarchy of 10 which is greater than 3 and so will drive the choice of HRG. Here are the relevant screens from NHS Ref, all accessed initially from the the OPCS screen.

You can now complete the coding document to determine the income generated by the provider trust.

Reference NHS - TheJcs

Post to Twitter Tweet This Post

{ 1 comment }

Refresh Multiple Pivot Tables – The Easy Way

by Dr Ang

I use pivot tables a lot. They drive multiple reports in my workbooks and it is not unusual for me to have 6 or so pivots in each spreadsheet. When the underlying data changes I spend quite a lot of time clicking through each pivot table to refresh it.

Refresh All Button to refresh all pivots at once

I was happy to discover the Refresh All button in excel 2007 today. Press it and all your pivots will refresh as if by magic!

You’ll find it on the Data ribbon.

Refreshing All Pivots using Macro Code

I also have a number of macro enabled workbooks that generate reports and update pivot tables. I have been writing code that specifies the pivot table by name and then refreshes it but it can be tricky to find the pivot table name. You can use refresh all within the code to refresh all pivot tables at once.

Try:

ActiveWorkbook.RefreshAll

or

Workbooks(1).RefreshAll

Originally published on my excelpivots blog.

Post to Twitter Tweet This Post

{ 0 comments }

Macro to Insert a "Last Update On:" Date Value

by Dr Ang

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:

Dim VERSIONDATE As String
VERSIONDATE = Format(Now, "dd-mm-yy hh-mm-ss")
Range("B3").Value = VERSIONDATE

Post to Twitter Tweet This Post

{ 0 comments }

Sumproduct Function and Conditional Criteria

by Dr Ang

The sumproduct function is one of the most useful and powerful features of excel but the online help available from microsoft is unfortunately pretty poor.

It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria.

Here is the simple formula layout or syntax:

=SUMPRODUCT((Condition 1)*(The range to sum))

or slightly more complex:

=SUMPRODUCT((Condition 1)*(Condition 2)*(The range to sum)

the “*” symbol between conditions represents an AND a “+” symbol would represent an OR.

Here is a simple example, of a schools course grades displayed in the following spreadsheet table,

You will probably want to summarise this to show the total number of students passing the course in each grade.

This makes use of the simple SUMPRODUCT formula in cell F2:

=SUMPRODUCT(($B$2:$B$15=E2)*($C$2:$C$15))

the use of absolute referencing here enables the formula to be copied all the way down the table.

In English the spreadsheet formula reads:

Sum the results in column C where the values in column B = “A”

Moving on to a slightly more complex example, we could increase the summary table to show each students performance across the different grades.

This requires the addition of another condition – namely STUDENT equals.

The spreadsheet formula in cell I2, which can again be copied across is:

=SUMPRODUCT(($B$2:$B$15=I$1)*($A$2:$A$15=$H2)*($C$2:$C$15))

In English the spreadsheet formula reads:

Sum the results in column C where the grades = “A” and where the student = “Adam”

To see these powerful excel functions in action and utilising the Condition 1 OR condition 2 terminology, download my example sumproduct.xls.

Post to Twitter Tweet This Post

{ 0 comments }

Macro to Consolidate Data Ranges from Multiple Excel Spreadsheets

December 4, 2011
Macro to Consolidate Data Ranges from Multiple Excel Spreadsheets

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

Read the full article →

The SUMIF Function using Named Ranges

December 2, 2011
The SUMIF Function using Named Ranges

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

Read the full article →

Waterfall Charts that Cross the X-axis

November 27, 2011
Waterfall Charts that Cross the X-axis

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

Read the full article →

How to Test for an Integer Value – detecting fractional activity counts

November 24, 2011
How to Test for an Integer Value - detecting fractional activity counts

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

Read the full article →