Prepare a Calculations Sheet

Quick reference

• 00:04 In this lesson, we're going to carry on putting together our Excel dashboard.
• 00:08 In the previous lesson, we created all of the pivot charts and
• 00:11 the pivot tables that we're going to need.
• 00:13 And now, in this lesson, we're going to create our calculations.
• 00:17 And notice here in the workbook, I've just added another worksheet, and
• 00:21 I've called it calculations.
• 00:23 And I've got some headings in here.
• 00:25 Now, on the dashboard that I want to create,
• 00:28 I have four main headings at the top displaying important key statistics.
• 00:33 And I've got those key statistics listed out in this spreadsheet.
• 00:36 So I want to be able to see at a glance on the dashboard the highest selling
• 00:41 item type, the highest profit by country, the amount of units sold for
• 00:45 2021 and also 2022.
• 00:48 Now, in order to get these figures and display them in nice cards at the top
• 00:51 of the dashboard, we need to perform some calculations.
• 00:54 And I would always recommend that any calculations you need to do go
• 00:58 on a separate calculations worksheet in your workbook.
• 01:01 So keep them separate from all your pivot tables, pivot charts, and
• 01:05 the dashboard itself.
• 01:06 And the first calculation we need to do here is we need to find
• 01:10 from our Source Data what the highest selling item type is.
• 01:13 So in order to do this, I need to first pull out a unique
• 01:17 list of all of the item types that we have in our Source Data.
• 01:22 Now, for this, I'm going to use the UNIQUE function.
• 01:25 Now, the only argument we really need to provide here is the array.
• 01:29 So where are our item types located?
• 01:32 So let's jump back to our Source data, and it's this column I want here, column C.
• 01:39 Now, I'm going to select this cell range as opposed to the actual
• 01:42 column because I have this big heading at the top here, and
• 01:44 I don't want that interfere with my formula.
• 01:47 So just to be on the safe side, we're going to click in cell C7,
• 01:51 Ctrl+Shift+down arrow to select all of those item types.
• 01:55 Let's work up in the formula bar.
• 01:57 All we need to do is close off the UNIQUE function, hit Enter.
• 02:01 And you can see we now have a unique list of all of the item types from
• 02:05 the Source Data.
• 02:06 So now that we have these,
• 02:08 we want to work out the total units sold for each of the item types.
• 02:13 Now, this is where we can use the SUMIF formula.
• 02:18 And what SUMIF does is it basically performs a calculation based on criteria.
• 02:24 So we have three arguments, range, criteria, and sum_range.
• 02:28 And a way that helps me understand how this works is I look at the second
• 02:31 argument first of all, the criteria.
• 02:33 What is our criteria?
• 02:36 Well, our criteria in this example is the Item Types.
• 02:40 So for this first one, it's Baby Food.
• 02:42 So the range is going to be wherever we're going to find this item type.
• 02:47 So if we jump back to our Source Data, our range is the Item Type.
• 02:53 Now, for this, I am going to select the column.
• 02:55 So you can see here, we have the Item Type column,
• 02:57 hover your mouse over the top of it until you get that downward-facing black arrow.
• 03:01 Click to select the entire column, comma.
• 03:06 Now, we've selected our range, we need to specify our criteria.
• 03:11 So the criteria that we're looking up in that range is Baby Food for
• 03:16 this first example, comma.
• 03:18 And then we need to provide the sum_range.
• 03:20 So what are we adding up?
• 03:22 Well, in this example, we want to calculate the units sold by Item Type.
• 03:26 So our sum_range is, let's jump back to our Source Data, the Unit Sold column.
• 03:33 And once again, I'm just going to select the entire column,
• 03:37 close off the bracket, hit Enter, and I get my total.
• 03:41 Let's double-click to copy this down to get the other totals for
• 03:45 the other item types.
• 03:47 So now that we have this information,
• 03:49 we can very simply calculate what our highest selling item type is.
• 03:53 Now, the first thing I'm going to do here is
• 03:56 I'm going to work out what the maximum value is in the Units Sold range.
• 04:01 So that's going to tell me what the highest number is.
• 04:04 The next thing I want to do is work out which item type
• 04:08 corresponds to this value just here.
• 04:11 And for this,
• 04:12 we're going to use a LOOKUP, you can use whichever LOOKUP function you like.
• 04:15 I'm going to use XLOOKUP.
• 04:18 My lookup_value is going to be the maximum value that we have in cell C7.
• 04:24 My lookup_array is where I'm looking for that value.
• 04:28 So I'm looking for that value in this Units Sold range.
• 04:33 And then finally, I just need to specify where my answer is, what I want to return.
• 04:38 So I want to return the actual item name, so that's this range of cells just here.
• 04:44 Close the bracket, hit Enter, and now I get what I'm looking for.
• 04:49 And this is the information that I'm going to pull through to become
• 04:52 a high-level statistic in my dashboard.
• 04:54 So let's go through and
• 04:56 complete the rest of these because they work along similar lines.
• 04:59 So the next thing I want to find is the highest profit by country.
• 05:02 So let's click over here.
• 05:04 Once again, I'm going to drag out my Source Data,
• 05:08 a unique list of all of the countries.
• 05:10 So we're going to type in UNIQUE, and
• 05:13 I'm going to select the country cell range, Ctrl+Shift+down arrow.
• 05:18 Close the bracket, hit Enter, and now I have my unique list of countries.
• 05:24 We now want to find out what the profit is for each of those countries.
• 05:28 So we're going to use the SUMIF formula again.
• 05:31 My criteria is going to be the country, so
• 05:34 my range is going to be wherever I'm going to find this information.
• 05:38 So let's jump back across the Source Data and
• 05:42 select the Country column, comma, that's the range.
• 05:46 Our criteria is the country, and the sum_range is what we want to sum.
• 05:53 So this time, we want to sum the profit.
• 05:55 So back to Source Data, and we want to select the Total Profit column.
• 06:01 Close the bracket, hit Enter, and then of course,
• 06:05 we can clickety-click to copy that down.
• 06:08 And I'm going to use the same method, so
• 06:11 I'm going to find out what the maximum value is for this list of profits.
• 06:18 And then we're going to use LOOKUP functions to extract the country that
• 06:22 the maximum profit relates to.
• 06:23 And just to show you something a little bit different, I'm going to use INDEX so
• 06:27 match this time because I do realize that some of you probably don't have XLOOKUP.
• 06:31 So let's type in INDEX.
• 06:34 Remember, the first argument of index, the array,
• 06:37 is where we're going to find our answer.
• 06:39 So I want to return the country name, so we're indexing the country list.
• 06:44 I'm now working up in the formula bar, so let's press comma.
• 06:48 The row number, well, we need to use the MATCH function in order to do this.
• 06:52 So we're matching the value in cell C8.
• 06:58 We're matching it in the Profit column, Ctrl+Shift+down arrow+comma.
• 07:05 And we're doing an exact match, so we want a zero on the end there.
• 07:09 Let's close off MATCH, close off INDEX, hit Enter, and we get our result.
• 07:16 Now, the final two here, the units sold for 2021 and 2022, these look
• 07:21 like they would be really straightforward, but we do have a little bit of an issue.
• 07:26 So what I'm going to do is, over here, I'm going to type in,
• 07:29 because we only have two years, I'm going to type in 2021 and 2022.
• 07:33 Now, if I try and do a SUMIF calculation using the year that I have in
• 07:38 this cell as my criteria, if we take a look at the Source Data,
• 07:42 consider we don't have a column that just shows the year for each of these items.
• 07:48 We have an Order Date column and a Ship Date column that has the full date in
• 07:53 there, but nothing that just houses the year.
• 07:55 Now, we could get around this by performing a more complex formula, but for
• 07:59 me, the easiest thing to do here is just simply to add a column to the table
• 08:03 that shows the year.
• 08:04 And we're going to base the year off of the Order Date field.
• 08:08 And this is why it's so important to break up your data as much as possible.
• 08:12 So let's click at the end here, and we're just going to give it a column
• 08:16 heading of Year, and we're just going to use the YEAR function.
• 08:21 So we want to extract the year from the Order Date over here.
• 08:25 Let's close the bracket and hit Enter, and you can see we now have a Year column.
• 08:30 So this makes my calculation a lot more straightforward.
• 08:34 So now we can simply say =SUMIF,
• 08:37 the range is going to be my Year column, comma,
• 08:42 my criteria is going to be the year, and the sum_range,
• 08:48 well, we're looking for the unit sold.
• 08:52 So once again, let's jump across to our Source Data and
• 08:56 select the Units Sold column.
• 08:58 Close the bracket, hit Enter, and then we can copy this down.
• 09:02 And if we want to drag these up into the table so everything's together,
• 09:05 we can simply do a very straightforward cell-linking process.
• 09:09 So let's type in =, I'm just going to link to units sold for
• 09:14 2021 and the same for units sold for 2022.

Lesson notes are only available for subscribers.