Скачать или смотреть How to Use Formulas Across Sheets | Microsoft Excel

Get your IT Team trained — request a demo today: https://training.cbt.gg/getdemo

CBT Nuggets trainer Simona Millham covers a special Excel skill called compilation, which is how to take data from multiple sheets, add it all up, then display the results in one place. There are a few different ways to do it! Simona shows you the right method for when your sheets are identical, when they’re different, and when you need more detail.

To start with, you’ll need some data to work with. You can create an Excel spreadsheet of your own with different sheets for different months of the year or watch along as Simona navigates her spreadsheet in front of you. Imagine you need to count up all your month-by-month production statistics and add them up into a quarter sheet.

In Excel, by shift-clicking multiple sheets and then highlighting multiple fields, you can input one value and press control-enter to automatically apply identical values into all highlighted fields across multiple sheets.

When applying a formula across multiple sheets in Excel, an exclamation point “!” identifies the name of the sheet the cell is being selected from. Once you’ve applied a formula across multiple cells in multiple sheets, you can click and drag the bottom-right corner to apply that formula to neighboring cells instantly.

Unfortunately, this formula could end up being very long and hard to read if you have a lot of data coming in from many sheets. Instead, use the SUM formula. Usually, the SUM formula is used to add up a range of cells, but in this case you’ll be using SUM to add up a range of sheets.

Adding up data across multiple sheets only works if the sheets you’re drawing data from are identical. If your data isn’t in identical sheets, you have to change the process. And remember: formulas don’t take into account the heading of the data, they only add up the cell ranges that you specify.

If you want to consolidate data across multiple sheets where there is consistency of the labels just not in the same position, a different feature than SUM is needed. In the Data Ribbon Tab, there is a Consolidate feature and button. The Consolidate feature in Excel can output static numbers, or it can link to the source data.

0:00 – Introduction
0:15 – A sample Excel spreadsheet to work in
0:50 – How to automatically input identical data across multiple fields and sheets
1:25 – Using the “click and point” method of updating formulas
1:50 – What an exclamation point in an Excel formula means
2:30 – How to use the SUM function to add data across a range of sheets
3:40 – Excel by default is using relative references when a formula is repeated into multiple cells
4:50 – How to consolidate data across multiple sheets when labels are consistent but position is not
6:00 – Using the Consolidate feature in Microsoft Excel to consolidate by category
7:40 – Understanding the different checkboxes and use cases of Excel’s Consolidate feature

Watch this entire course: https://training.cbt.gg/toj

Not a CBT Nuggets subscriber? Start your free week: https://cbt.gg/2I5NxY1
-----------------
Connect with CBT Nuggets for the latest in IT training:
• Twitter -   / cbtnuggets  
• Facebook -   / cbtnuggets  
• Instagram -   / cbtnuggets  
• LinkedIn -   / cbt-nuggets  

#microsoftexcel #exceltutorial #microsoftoffice365 #ittraining #cbtnuggets