5 tips for Excel users when connecting to Power BI
Excel is not dead! We often notice organizations starting their self-service / Enterprise Data Warehouse (EDW) project by stating the goal ‘getting rid of Excel’. We at Lytix do agree that ‘standardized reporting’ (= same report on a timed schedule with the same manual transformations) should as much as possible be automated. On the otherhand, Excel does provide added value if you can use it as a one-time sandboxing environment, to store simple mappings (if a master data solution is too much overkill) or to search in long lists of values. Power BI indeed is a great tool, but some business users may be more attracted to the classical Excel cell-based way of working with calculations. This blog provides some general tips and tricks that can help you optimize your experience in these tools.
Excel Tip 1: Navigating through a long list of attributes and measures
When using the functionality ‘Analyze in Excel’, you’ll often be presented with a long list of tables, attributes and measures. The default visualization shows this long list on top, on the bottom you’ll have a matrix of the filters, columns, rows and measures of the PivotTable. You are just a click away of showing this a lot more convenient; right-click the gear button and organise this as ‘side-by-side’.
Excel Tip 2: Getting rid of the PivotTable’s Compact Form
If you’re working with larger volumes of data or need to perform a VLOOKUP / INDEX-MATCH on your data, you are probably tired of always re-formatting your PivotTable to a more tabular format. Following actions clearly show what Excel users have been doing more than a 1000 times in their life:
Did you know that you can change the default way that a PivotTable is shown in Excel? This super-handy feature is available in the latest versions of Excel and can be found on navigating to ‘Options > Data > Edit Default Layout’. These are the settings you should apply:
Excel Tip 3: Slicers with Multiple Connections
Few people know that multiple PivotTables can be controlled with one and the same filter. To do that, select one PivotTable and create a slicer with the desired filter. Once the slicer has been created, right-click it and select ‘report connections’. This opens a list of all Pivot Tables which are linked to this data source connection. Then select the other pivotable that you want to have linked to this filter. From now on, this filter controls both lists. Hint; you can always rename a PivotTable to make this selection easier.
Business users do love this feature and ‘slicers’ are a known functionality in Excel. What a lot of users do not know, is that they can severely slow down your refresh rate. Apply following steps to overcome this problem: Change the setting ‘Visually indicate items with no data’. If this is marked as active (which is the default), then Excel will fire several queries at your Power BI/Analysis model to verify if it should be greyed-out or not. This will often result in performance issues. It is a best-practice to disable this functionality when using slicers.
Excel Tip 4: Automatic refresh
If the same Excel file is used on a daily, weekly or monthly basis, then business users always need to repeat the steps of opening the Excel file (… which takes a while) and then pushing the ‘Refresh All’ button. Few users know that you can trigger the ‘Refresh All’ function right after the Excel file is opened. To do that, open the properties of the connection and enable the ‘Refresh data when opening the file’ option. There is also the option to refresh it every few minutes while you keep your file open.
If the same file is used repeatedly, why not convert it to a more standard way of reporting such as Power BI or Reporting Services? This way it is easier to keep the data up-to-date and the access roles can be configured properly.
Excel Tip 5: Show items with no data on Rows/Columns
By default, PivotTables only show values if a measure has one value linked to it. In the example below, we have eight different product categories. If we add a measure, two of those categories disappear as there are no values for these product categories. Often, business users try to solve this by adding more and more measures until eventually the value pops up (= dirty fix). Excel also provides you with a cleaner solution: open the PivotTable options, navigate to the Display tab and select the option ‘show items with no data on rows’. This functionality also works for columns.