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

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.

Wrap-up

Thanks for reading through this blog post, we at Lytix hope you’ve learned something new. If you need advice on your data & analytics landscape, send a message to Lytix or the author Sander Allert and we’ll be more than happy to introduce ourselves.