Excel "Best Practice Guidelines" to implement into business

[NickC]

Hi guys,

I want to provide a set of Excel "best practices" to the business, with a focus on pivot tables (drawing from a SQL Server Analysis Services cube).

Has anyone seen something like this put together before? I've done plenty of Googling but haven't been able to find anything suitable.

Even something like a "dos and don'ts" list would probably work.

Thanks
Nick

[Kamran_bsh]

There is a framework by the Institute of Chartered Accountants in England & Wales which highlights best practices for spreadsheet design. But, your focus is on pivot tables.
In my experience

1)You can use custom formatting options and design a template for pivot table formatting which will save you a lot of time in future and you won't be doing formatting every time. Templates work really well.

2) Try to keep data in rows rather than columns basically avoid flat tables

3) Make sure if source data is exported to excel it is in table format and the table has an appropriate name and all the fields have appropriate names. You can include this in your standard template as well.

4) Try to limit your use of unnecessary slicers because they will make your reports slow if there are massive data sets to analyse.

Above works well for me but we are using access as a database.

Hope that helps