Look Who it Was Random video

Excel - Remove the Word Total from Each Subtotal in Excel - Episode 791

538 views · Published 15 January 2009 · 2:15 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Welcome back to the MrExcel netcast! In this video, we'll be tackling a common issue that many Excel users face when working with subtotals. Have you ever needed to do a VLOOKUP to get the total revenue by customer, only to be met with the word "TOTAL" attached to the customer name? It can be frustrating and time-consuming to manually remove those extra characters. But fear not, I have a solution for you!

First, we'll create subtotals by customer, cost of goods sold, and profit by going to DATA, SUBTOTALS, and selecting the desired options. This will give us a great view of the data, but the issue arises when we need to do a VLOOKUP using the customer name. The "TOTAL" attached to the customer name will cause problems. So, my solution is to use the =LEFT function to grab only the characters we need.

To do this, we'll use the ALT+; shortcut to select all visible cells next to the cells with the "TOTAL" attached. Then, we'll use the =LEFT function and specify the cell we want to extract characters from, in this case, D6. But how many characters do we want? Well, we want the length of the entire string, which we can get using the L-E-N function, and then subtract 6 characters (the length of "TOTAL" plus a space). This will give us the customer name without the extra characters.

Once we have the correct formula, we can use the ALT+; shortcut again to select only the relevant cells and then copy and paste them onto a new worksheet. Now, we have our totals by customer with the original customer name, making it easy to do VLOOKUPs using the L-E-N function to get the total length of the customer name and then subtracting 6. This simple solution will save you time and frustration when working with subtotals in Excel.

Thanks for watching this netcast from MrExcel! Don't forget to like and subscribe for more helpful Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Creating subtotals by customer, cost of goods sold, and profit
(00:20) Using the number 2 view for data analysis
(00:30) Difficulty with VLOOKUP due to "AIG TOTAL" cell
(00:40) Solution: Selecting visible cells and using =LEFT function
(00:50) Using ALT+; to select visible cells
(01:21) Entering formula and copying to new worksheet
(01:53) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Copying and pasting data in Excel
Creating a new worksheet in Excel
Finding the length of a string in Excel (LEN function)
How to collapse data in Excel
MrExcel netcast on Excel tips and tricks
Removing specific characters from a cell in Excel
Selecting visible cells in Excel
Subtracting characters from a string in Excel
Using the LEFT function in Excel
Using VLOOKUP in Excel
Using VLOOKUP with LEN function in Excel
YouTube video on creating subtotals in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152088/

More from this channel