Look Who it Was Random video

Excel - Mastering Mixed Recording in Excel: Navigating, Adding Totals, and Formatting - Episode 811

478 views · Published 13 January 2009 · 3:12 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Mixed Recording in Excel: Navigating, Adding Totals, and Formatting.

Welcome back to the MrExcel netcast! In this episode, we're going to be talking about mixed recording in Excel. Many times when we're recording macros, we need to turn on and off relative references as we go. Today, we have a real-life scenario where we receive an invoice file from our IT department and need to format it by adding totals. But here's the catch - the total row is different every time. So, we need to think about whether we want relative reference on or off while recording.

To start, I'm going to click on the RECORD MACRO button and name it "FORMATINVOICE". Remember, you can't have any spaces in the macro name, so I just capitalized the "I". Next, I'll do CONTROL+SHIFT+T to add the totals and store the macro in THIS WORKBOOK. Now, we need to get to cell A1. I could either turn off relative references and click on A1, or use the GO TO button by pressing CONTROL+G and typing in A1. This will be recorded as an absolute reference, regardless of whether relative recording is on or off.

Next, we need to get to the bottom of the data set. There are two ways to do this - either by pressing CONTROL+DOWNARROW or the END key followed by the DOWN ARROW. Once we're at the last row of data, we'll press the DOWN ARROW one more time to get to the blank row. Here, we'll type in the word "TOTAL" and then select all three cells for PRODUCT REVENUE, SERVICE REVENUE, and PRODUCT COST. Then, we'll go to the formulas tab and hit the AUTOSUM button to add the totals in.

At this point, we may want to go back up and format row 1. To do this, we can turn off relative reference, click on A1, select all the cells in row 1, and then go to the format tab and choose "column" and "autofit" to make everything long enough for the headings. Once we're done, we can stop recording. Now, let's try this macro out on a different day with a different data set. We'll go to Tuesday and notice that we have more rows this time. When we run the macro, we're hoping that it goes to row 18 instead of putting the totals in row 14. And it does! The macro recorder goes to row 18 and then comes back up to row 1 at the end. Everything looks like it's working just fine.

However, we need to come back and see why the AUTOSUM button does not work in the macro recorder. So, stay tuned for our next episode where we'll dive into that issue. Thanks for stopping by and we'll see you next time for another netcast from MrExcel! Don't forget to like, comment, and subscribe for more Excel tips and tricks.

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/


Part 1 of 2: Many times, you have to use a clever mix of relative and absolute recording to get the macro to perform certain tasks. The goal today is to navigate to the bottom of a data set, add totals, and then move back to row 1. Episode 811 will show you how to handle the relative button, but watch out, as episode 812 will reveal yet another problem.


Table of Contents:
(00:00) Introduction to recording macros
(00:22) Formatting an invoice file
(00:32) Adding totals with relative reference
(01:04) Navigating to specific cells
(01:24) Formatting row 1
(01:38) Stopping the recording
(02:13) Testing the macro on new data
(02:32) Explanation of why the AUTOSUM button does not work in the macro recorder
(02:52) 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:
Adding totals to varying rows in Excel
Excel macro recorder limitations
Excel tips for working with large datasets
Formatting an invoice file in Excel
Formatting cells in Excel using autofit
How to turn on/off relative references in Excel macros
MrExcel netcast on Excel automation techniques
Troubleshooting issues with the AUTOSUM button in Excel macros
Understanding absolute and relative references in Excel
Using the GO TO feature in Excel macros
Using the RECORD MACRO feature in Excel
YouTube tutorial on recording macros in Excel

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

More from this channel