Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.
As mentioned on the show, you can receive my new book - 1 chapter a week - for free via e-mail. Visit this page to sign up.
Today we have a list of sales by store. You need to add a subtotal for each store. This is fairly easy to do. Sort the data by store. Go to the first row of data from the next store and select Insert - Row.
Type a label in column A indicating that this is Store 100 Total. In cell C14, select Alt+Equals to put in a sum formula.
Drag the fill handle (the square dot in the lower right corner of the cell pointer) to the right to copy the formula over to column D.
This is not hard. It takes maybe 20 seconds per store. However - if you have 250 stores, it will take a really boring 75 minutes to add all of those totals. Luckily, there is an easier way!
Select a single cell in your data. From the menu, select Data - Subtotals. The following dialog box will be displayed.
The Subtotals dialog offers to insert subtotals every time that the Store changes. By default, the final column is checked in the Add Subtotal To: section. I also checked units. Click OK. In less than 15 seconds, Excel has inserted hundreds of subtotals for you!
You can also use this feature to do multiple subtotals. Be sure to start at the higher level first. If you wanted subtotals by province and store, first add them for the province.
Then, select Data - Subtotals again. This time, uncheck the box for "Replace Current Subtotals". Change the top dropdown from Province to Stores.
The result - subtotals are added for each store and province.
Look to the left of column A. There are new buttons there labeled 1, 2, 3... These are called the Group & Outline buttons. If you choose button 2, you will see just the province subtotals.
If you choose the #3 group and outline button, you will see all of the subtotals.
The automatic subtotal feature has been around since Excel 97, but it is often overlooked. In the case like today, it can turn a boring 1-hour+ process into a few seconds of work.
For the BEST TV show on technology, check out Call for Help.
This tip was originally published on January 21, 2005 and aired on TechTV on February 8, 2005. The permanent URL for this page is http://www.mrexcel.com/tip085.shtml.
If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.