From the course: Excel: You Can Do This
Linking sheets - Microsoft Excel Tutorial
From the course: Excel: You Can Do This
Linking sheets
(dramatic music) - You have multiple sheets in a workbook, and we want to link those sheets. Data is not always on one sheet nice and neat, no the world is not that simple, folks. So we have things on multiple sheets. And here, I'm going to show you about making a summary sheet, that's linked to two other sheets, and is integrated, and accurate and easy. Let's look here we go. On the Tuesday sheet, we have the people who attended and the fee that they're paid. Thursday, same thing more people and their fees. We don't want to combine all of this on one sheet. That's why we're going to make a summary sheet, and we go back to what's called summary. We want the total fees from Tuesday. We're going to do equals sum open parentheses. While the parentheses is open, go to the Tuesday tab, highlight the range of fees, Enter. It bounces back to the summary sheet, and we see $4,175. Now we need to do the same for Thursday. Equals, sum open parentheses, go to the Thursday tab, highlight the fees, Enter, $4,150. Now we need a count of who attended each day. Equals, counta. What counta does for us is it counts every non-blank cell, in the highlighted range. So we're going to go to Tuesday, highlight the names, Enter, 13 people. Thursday, equals counta, open parentheses, Thursday highlight the names, enter 13 people on Thursday, Now here's a bonus tip for you. I'm going to do a double window, so we can see what's going on. View new window. Now I can see side by side, and when I make a change, because we realize that Benji actually did not attend, so I'm going to highlight Benji and this fee, right click, delete, I want to shift sales up, and then watch on our summary tab. The changes are going to happen automatically. Okay, look at, 3775 for fees on Tuesday, and only 12 people attended on Tuesday. That's what we get by linking these worksheets, we keep everything tight and accurate. And when we make a change, it happens everywhere that's relevant. (dramatic music)
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
-
-
Flash Fill: Merge data in different cells1m 51s
-
Linking sheets3m 3s
-
(Locked)
Copying, coloring, and naming tabs3m 3s
-
(Locked)
SUM: Calculating a basic sum2m 12s
-
(Locked)
Freeze panes: See your data while scrolling1m 57s
-
(Locked)
Conditional formatting2m 28s
-
(Locked)
Data validation: Ensure users don't input crazy data2m 23s
-
(Locked)
Create a drop-down list3m 27s
-
(Locked)
Create basic charts from scratch1m 38s
-
(Locked)
Split text in Power Query: Split real-world data fields2m 53s
-
(Locked)
IF statement2m 4s
-
(Locked)
COUNTIFS: Count entries based on conditions1m 40s
-
(Locked)
The #N/A error: What does it mean and what causes it?1m 45s
-
(Locked)
VLOOKUP4m 18s
-