Excel "Best Practice Guidelines" to implement into business

Use this forum for any general discussion and questions about Excel
NickC
Posts: 38
Joined: Fri Aug 22, 2014 5:55 pm
Location: Adelaide, South Australia

Excel "Best Practice Guidelines" to implement into business

Postby NickC » Tue Aug 14, 2018 8:53 am

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
I'm currently using 64-bit Excel 2016
Adelaide, South Australia

Kamran_bsh
Posts: 10
Joined: Wed Jul 25, 2018 8:54 am
Location: England

Re: Excel "Best Practice Guidelines" to implement into business

Postby Kamran_bsh » Wed Aug 15, 2018 6:57 am

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


Return to “Excel”

Who is online

Users browsing this forum: No registered users and 0 guests

cron