From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365
Unlock the full course today
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Summarize data from multiple ranges by using the Consolidate feature
From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365
Summarize data from multiple ranges by using the Consolidate feature
- [Instructor] The consolidate feature pulls data from multiple Excel worksheets into one worksheet. The caveat is that the data must be similar. I'll show you options you can choose from when setting up the consolidation and show you how you can link to the data source. Let's take a look at our Consolidate workbook. We have 2017 sales and notice the layout of the data. We have 2018 sales and I will say it makes it a lot easier to consolidate the data if it is laid out in the same manner. All of my labels are the same, the columns are in the same order, and it'll just make it easier when we combine it onto our Combined Sales worksheet. I'll go ahead and select the range that I want to pull the information in from. So quarter one will be pulled from 2017 sales quarter one, 2018 sales, we're going to have Excel add those together and put them in the quarter one column. On the data tab, let's go to Consolidate. These are the…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
Use AND(), OR(), and NOT() in nested functions6m 9s
-
Use IF() and IFS() in nested functions4m 31s
-
(Locked)
Use SWITCH() in nested functions3m 38s
-
Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions5m 2s
-
(Locked)
Use COUNT() and COUNTIF()2m 54s
-
Use MAXIFS() and MINIFS() in nested functions3m 27s
-
(Locked)
Look up data using VLOOKUP() and HLOOKUP()5m 52s
-
Use the MATCH() and INDEX() functions5m 47s
-
(Locked)
Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions8m 15s
-
(Locked)
Summarize data from multiple ranges by using the Consolidate feature3m 10s
-
(Locked)
Use What-If Analysis Goal Seek and Scenario Manager5m 57s
-
(Locked)
Calculate financial data with NPER() and PMT() functions3m 30s
-
(Locked)
Troubleshoot formulas9m 24s
-
(Locked)
Challenge: Create advanced formulas27s
-
(Locked)
Solution: Create advanced formulas9m 8s
-
-
-
-