Tutorial: How to align a vertical line chart to a table in PowerPoint and make it dynamic regarding number of rows

Time needed: 30 minutes

1. PART 1: Creating the Excel workbook with the chart and table calculations

   An important note upfront: The sales data comes within a table called Data. When referencing columns within this table in a formula, Excel puts the column names in brackets. So for example, Data[SalesTerritory.Country] refers to all data from the SalesTerritory.Country column within the table called Data.

2. Obtaining the country list

   – Open the Excel workbook.
   – Create a new worksheet.
   – Add the category list using the formula below.
   
   =UNIQUE(Data[Product.Category])

3. Getting the count of subcategories for each category

   This step helps here to determine the maximum number of rows for the table in PowerPoint. This tutorial leaves out the case where there is more data than rows. For these cases there is an explanation on how to distribute the data across multiple slides.
   
   Use the following formula to determine the number of subcategories per category:
   
   =BYROW(B4#,LAMBDA(category,COUNTA(UNIQUE(FILTER(Data[Product.Subcategory],Data[Product.Category]=category)))))
   
   It works as follows. The BYROW function is applied to the list of categories. After that, it applies the LAMBDA function to each category of this list. Within the LAMBDA function a mere COUNTA counts the number of UNIQUE subcategory entries. The FILTER function makes sure that only subcategories from the current category (with respect to the LAMBDA function) are counted.

4. Calculating the relative margin per category

   With the category list in place, the actual data gets into the focus. In particular, the line chart shall show the category margin in percent as comparison. So we divide the margin in absolute terms by total sales. The SUMIFS functions does the job for each category.
   
   =
SUMIFS(Data[Margin abs],Data[Product.Category],B4#)
/SUMIFS(Data[Sales Amount],Data[Product.Category],B4#))

   Add this formula to the worksheet to calculate margin % per country and month.

5. Defining the Master Cell

   For the iteration loop over the categories the Master Cell will hold the current category. Therefore, we just enter one of the categories here to make the subsequent creation of the workbook easier.
   

6. Looking up relative margin for current category

   We will merely lookup the relative margin from the range calculated before:
   
   =XLOOKUP(G4,B4#,D4#)
   
   Of course, calculating it with SUMIFS based on the raw data here is an equally sound idea.
   

7. Getting the subcategory list for the current category

   As the objective is to show the margin % per subcategory, we look up the subcategories first of all:
   
   =SORT(UNIQUE(FILTER(Data[Product.Subcategory],Data[Product.Category]=G4)))
   

8. Calculating the relative margin per subcategory

   With the list of subcategories in place it is simple to obtain the relative margin of each:
   
   =
SUMIFS(Data[Margin abs],Data[Product.Category],G4,Data[Product.Subcategory],J4#)
/SUMIFS(Data[Sales Amount],Data[Product.Category],G4,Data[Product.Subcategory],J4#)
   
   It is done in a similar fashion as before for the category by using SUMIFS.
   

9. Creating the chart data, first a helping row number

   First of all the row subcategory number for the given category will be calculated. This is optional as it is not used by any other formula subsequently in this tutorial.
   
   =SEQUENCE(ROWS(J4#))
   
   

10. Calculating the vertical position of each subcategory data point in the vertical line chart

    This is a really important step of the tutorial as it calculates the vertical position for each data point between values of 0 and 1. Reason for this is that the chart Y axis determines its lower and upper boundaries based on actual data values. However, this is impossible to predict, so fixing the lower and upper boundaries to constant values becomes inevitable. We chose the values 0 and 1 here because this way it is easier to calculate.
    
    =LET(countOfRows,ROWS(J4#),SEQUENCE(countOfRows,,1/(countOfRows)*0.5,1/(countOfRows)))
    
    This formula also takes into account that both the first and the last data points will be in the middle of a table row.
    
    

11. Getting relative margins per subcategory from before as X axis values.

    For the chart data area we will merely get the relative margins per category as calculated before.
    

12. Getting Y position for category comparison line.

    For the comparison line it is possible to use the Y values as before. However, this might look odd as the data points for the subcategory series are supposed to be position in the middle of each row. Instead, we want to have a vertical comparison line which reaches from the topmost area of the chart to the bottommost. Accordingly, we create the Y axis values in a straight forward way starting at Y=0 reaching to Y=1:
    
    =LET(countOfRows,ROWS(J4#),SEQUENCE(countOfRows,,0,1/(countOfRows-1)))
    

13. Getting relative margin of category as X axis values.

    For the comparison line, we need the same X value for all rows. This makes sure that the comparison line will be vertical:
    
    =SEQUENCE(ROWS(J4#),,H4,0)
    
    
    IMPORTANT (as not shown in the screenshot). The header row (in the screenshot cell R3) contains a concatenation of text and the current category name:
    
    ="Category: " & G4
    

14. Adding some helping numbers to easily create the data range for the scatter plot chart later on.

    This step is optional. It helps to clearly see how over how many rows the data for the chart might stretch.

15. Creating a scatter plot chart with lines and markers

    

16. Setting X and Y value data ranges for the subcategory series

    To have the series for the subcategories in place, we set up the series as follows. The calculated Y axis values needs to be set as series Y values and the relative margins as series X values.

17. Setting X and Y value data ranges for the category comparison line

    Also for the category comparison line we set up the series in the same way. Consequently, the calculated Y axis values need to be set as series Y values and the category margin as series X values.

18. Setting up the chart Y axis

    As explained above, we calculate the Y axis values in an interval between 0 and 1. Accordingly, the automatic bounds calculation needs to reflect these values instead of the automatic setting. Additionally, we need to reverse the Y axis as the first row has the value 0 (and not 1). The last setting we apply here is to set the Axis Crossing to the Maximum axis value instead of Automatic. This makes sure that the X axis labels are at the bottom of the chart.

19. Fine-tuning the chart area for proper looks

    So far the X axis values showed decimals. As the margin is a percentage value this needs to be changed here, too. Additionally, it is necessary to hide the markers from the comparison line and to make some room at the top of the chart for a helpful data label (see below).

20. Creating the Data Label for the topmost data point

    Select the topmost data point and right click and hit Add Data Label.

21. Formatting the Data Label

    The Data Label we position above the data point and switch to Series Name instead of some value. This way it is always clear what the meaning of the vertical comparison line is.
    
    Note: Setting the Data Label for the topmost data point is the only way it makes sense here. The topmost data point will be always there no matter how many rows (at least 1, of course) the chart will show. To have the Data Label at the bottom the Y axis must not be reversed and the Y axis values have to be decreasing from 1 to 0.
    

22. Creating the labels for the data table

    The first column of the data table is supposed to contain the subcategories. We merely take the labels from before.

23. Getting and formatting the margin % per subcategory for the data table

    The TEXT formula helps to format the relative margin as percentages:
    
    =TEXT(K4#,"0.0%")
    

24. Adding some helping numbers to easily select the range for the data table later on.

    This step is optional. It helps to clearly see how over how many rows the data for the table might stretch.
    
    This step completes the Excel workbook.
    

25. PART 2: Creating the perfectly aligned table and vertical line chart combination in PowerPoint

26. The target for the automation is a slide in a PowerPoint presentation with a fixed title.

    

27. Adding a table to the template slide

    – Choose the insert tab in the PowerPoint ribbon
    – Hit “Insert Table”
    – Create a table with 3 columns and 15 rows. As 14 is the maximum number of data points / rows for this example, the 15th row is for the header. The 3 columns here are for category name, margin % and mere background for the chart.
    

28. Setting Cell Margins to narrow to allow reducing row height

    

29. Removing all cell borders.This is superfluous due to banded rows of the table.

    

30. Setting the vertical alignment to have the texts in the table properly align with the data points of the chart.

    

31. Linking the PowerPoint template presentation with the Excel workbook

    – Open the SlideFab tab in the ribbon.
    – Click the small chevron (down angle) button where it says “Excel: Closed”.
    – Choose either of the first three entries:
    — “Select and Open Excel file” will let you browse the hard drive for the Excel file.
    — “Open Excel file” will re-open an existing file, if there had been one already. If not, it has the same effect as the one mentioned before.
    — “Try to use currently open Excel workbook” will try to establish a connection with the Excel workbook which was active most recently.

32. Copying and pasting the vertical line chart from Excel to the PowerPoint slide

    This way the pasted chart still has the connection to the Excel data.

33. Aligning the vertical line chart and the data table

    – Put the vertical line chart in front of the table (ensure that chart is fully in front)
    – Use the Y axis bounds of 0 and 1 to perfectly align with the upper and lower edge of the first and of the last data row.
    

34. Removing Y axis and horizontal lines from the chart

    
    

35. Adding manual table headers and colouring the Data Label of the chart accoringly

    

36. Connecting the Excel data table range with the table on the PowerPoint template slide

    – Select the range containing the table data all the way to down to the last row in which we expect data.
    – Select the target table in PowerPoint
    – Click the cross hair icon in the “Excel Source Range” to define the link. SlideFab will write the address of the selected range into the control field.
    – Set the “Start Row in PPT table” to 2. This will preserve header row in the PowerPoint table.
    – Enable “Remove Empty Rows”.
    – Enable table stretching with the setting “Start row and below” which will make sure that SlideFab stretches data rows only. The header in row 1 will remain as defined.
    

37. Just a reminder: The pasted chart in PowerPoint is already set up for automation.

    

38. PART 3. Setting up the iteration loop

    So far we built the Excel workbook and the PowerPoint template slide. We also linked the vertical line chart as well as the table. The only thing missing is setting up the automation for creating multiple combined vertical line chart with table.

39. Setting up the Candidate Range for the category rows

    – Select the column with unique identifiers (here the category name is a unique identifier for each worksheet).
    – Open the Iteration Loop side pane via the ribbon.
    – Click the crosshair icon in the “Loop Candidate Range” control to define the link. SlideFab will write the address of the selected cells into the control field.

40. Setting up the Master Cell for the current category name

    – Select the cell into which one value (i.e. category name) from the Candidate Range shall be written.
    – Click the crosshair icon in the “Loop Master Cell” control to define the link. SlideFab will write the address of the selected cell into the control field.

41. Running the automation

    – Click the “Make Slides” button in the SlideFab tab of the Powerpoint ribbon to open up the actual window for this.
    – When hitting the “Start” button, SlideFab will run the automation. It will work even without an Iteration Loop using the current state of the Excel workbook.

42. Waiting some brief moments until the slide automation completes.

    

43. Rerun the slide making automation as explained above and check the output.

    Perfect. The created combination slides with vertical line chart and table are precisely aligned.

44. The closeup of one of these slides reveals that the data points are exactly in the middle of each row.