Excel: Mapping, Grouping and Summarizing data in MS Excel (Tutorial)
DescriptionThe video is showing how to use the mapping technique in Microsoft Excel to summarize data. The data being used is a large set of sales from January 1st, 2018 to December 31st, 2018, of various grocery items. The goal is to create a table that shows the total sales of each grocery item, grouped by category and by month. To do this, the video goes through the following steps:
1. Map the grocery items to their respective categories by copying and pasting the names of categories in front of the grocery items.
2. Use the vlookup formula in the sales sheet to bring the category names in front of their relevant grocery item.
3. Extract the month names from the date column in the sales table using the text formula.
4. Use the sumifs formula in the summary sheet to fill the table, summing the sales column in the sales sheet by category and month.
5. Drag the formula from January to December, and from Bakery to Produce, leaving only the total row and the total column.
6. Or alternatively you can also use a pivot table to do the same.
#excel #ExcelMapping #DataSummarization #GrocerySales #VLOOKUP #TextFormula #SUMIFS #PivotTable #ExcelTips #DataAnalysis