Tip #1: Don’t pull in pre-calculated data
Because when you summarize price, in-stocks and sales velocity in your scorecards the results will be wrong. Instead, you should only pull in raw numbers, then apply the calculations using formulas or pivot table value field settings.
Tip #2: Avoid manual cell references
Lookup formulas and pivot tables are wildly powerful. However, data sets grow, and you need to update all your formulas. Worst case scenario, your team forgets, leaving you with incorrect data. Best case scenario, you are wasting time updating references which can lead to errors within your report. Instead, use named ranges for these formulas, created by selecting a range of cells and right-clicking and naming the name. Now you have a consistent set of data across multiple formulas, worksheets and pivot tables that will grow as the rows in your data set grow.
Tip #3: We all love to merge and center cells, but there’s a better way
Merge and centering cells makes using your report a nightmare, as simple Excel tasks like copying/pasting, moving cell contents, and selecting a group of cells will no longer work. Using “center across selection” achieves the same clean look without sacrificing ease of use. Select cells, click Ctrl+1 and change the horizontal alignment.
Tip #4: Avoid external file references
Make sure all necessary data is contained within one workbook and remember to paste as values when you need to move data from one workbook to another. External file references will break as soon as a team member tries to open the report and makes formulas much more error prone even if your report is for personal use only.
Tip #5: Set pivot tables to auto refresh, otherwise you’ll get bad data
End users won’t remember to do it every time and this will lead to them making decisions based on out-of-date data. Do this by setting pivot table data options to “Refresh data when opening the file” and set “Number of items to retain per field” to “None”.
Tip #6: Steer clear of large file sizes
The best practice is to keep the excel files 20MB in size so you can send them across email providers. Plus, we want to keep the files smaller so they’ll be much faster for your users. A way to do this is to filter to only see active items, or items with sales, when pulling data from retailer systems. Also choose data at the right level. For example, if you’re doing an Item level report, don’t pass store level data in because this is the most granular = more rows = largest files.
