How to Consolidate Data in Excel

This video features an excel function that is used to consolidate data from one workbook, or from different open workbooks. You can simply click ALT-A-N and you will be directed to the consolidate window. You can also find it under the data tab.

To consolidate data from 1 sheet, click the keyboard shortcut ALT-A-N. Use the default function sum, but you can change it if you want to do other functions from the drop-down list like count, average, max, min and many others. Then input the reference. Below that says, use labels in, and choose left column. And you will have the consolidated figures.

If you do consolidation of data from different sheets, you still have to click the same keys, Alt-A-N, click the reference box and select the amounts in Sheet 1 as the first reference. Click add, and the first reference is added to all references box. Put the cursor back to the reference box and choose the numbers from sheet 2 as the 2nd reference, then click add again and it’s transferred to all references. Then repeat the process for the 3rd reference and do the same for the succeeding references if any. That’s it, and you have the consolidated figures.

There’s more. If you want to show the links or the sources of the data, you can tick “create links to source data”. That’s excellent.

Title: How to Consolidate Data in Excel

#howtoconsolidatedatainexcel
#keyboardshortcuttoconsolidatedata
#consolidatedatawithinthesameworksheet
#consolidatedatafromdifferentworksheets