Welcome to Excel Jet Consult blog post. In this tutorial, we learn how to create a Dynamic Report using SUMIFS Across Workbooks and Sparklines. Let’s get started
Video
Sample Dataset
In the screenshots below, we have a sample dataset that is officially formatted an an Excel Table
And below, we have the Sales Report worksheet where we want to write our formula from the Sample Dataset above
Arrange The Workbooks
Before we write our formula across the workbooks, it is necessary to place the two workbooks side by side for easy navigation. Therefore, to arrange side by side,
- In the Window group of the View tab click on Arrange All
- Select Tiled and Click OK
From the screenshot below, we can see the workbooks side by side
SUMIFS Formula
Next, we want to use the SUMIFS function to write our formula. At the end of the formula, we want to see the Total Sales by Year and Brand
- In cell B8, execute the formula: =SUMIFS(‘Sales Data.xlsx’!Table1[Total],’Sales Data.xlsx’!Table1[Year],A8:A18,’Sales Data.xlsx’!Table1[Brand],B7:H7)
- Click Enter
From the above formula, we can see the name of the source data workbook as well as other attributes such as the sheet name, table and column names respectively.
From the screenshot below, we can easily see the Total Sales by Year and Brand
Sparklines
Sparklines are mini in-cell chart that allows us to spot trend in our dataset. To do that,
- Select cell I8 and click on Column Sparklines under the Insert tab
- In the Create Sparklines dialogue box, select the data range from B8 to H8
- Click OK
From the screenshot below, we can see the sparkline in the cells
Manage Links
To manager links from the linked workbooks:
- In the Queries and Connections group of of the Data tab, click on Edit Links.
Then, we can do things like Update Values, Change Source Data, Open Source, Break Link, Check Status as seen below
See you in the next tutorial