Look Who it Was Random video

Excel - Master Adding Headings to Repeating Data Sets in Excel | Excel Tutorial - Episode 546

537 views · Published 29 July 2009 · 3:03 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Adding Headings to Repeating Data Sets in Excel.

Welcome back to the MrExcel netcast, where we share tips and tricks to help you become an Excel pro. In this episode, we'll be discussing how to add headings to a data set that has repeating headings throughout. This can be a tedious task, but with the right technique, it can be done quickly and efficiently.

During one of our power excel seminars, a participant asked how to add headings to a data set that has repeating headings every time the group number changes. This is not a common way to set up data, but it can happen. The participant wanted to avoid manually copying and pasting the headings to each row, which would be extremely time-consuming in a 150-page document. So, we took a look at the report and came up with a solution.

The first thing I noticed was that the headings were in row 2 and there was a title in row 1. My initial trick would not work with these two rows adjacent to each other. So, I inserted a row to keep the data apart and turned on the auto filter. By going to Data ＞ Filter ＞ Auto Filter, we were able to get drop-down headings on each of the cells. Then, I asked if the heading in column B, "customer", appeared anywhere else in the data set. The answer was no, so we proceeded with our solution.

Next, we selected all the cells next to the headings and copied them to the clipboard. But, we needed to make sure we only pasted the headings to the visible cells. To do this, we used the shortcut ALT + semicolon or went to Edit ＞ Go To Special ＞ Visible Cells Only. This ensured that the headings were only added to the visible cells, which were just the headings for the sales data. Finally, we turned off the auto filter and added the new headings to the entire data set.

This may seem like a complicated process, but it's a great solution for when you need to add headings to a data set with repeating headings. Remember to use the ALT + semicolon shortcut to select visible cells only and make sure there is a blank row between the title and the headings for the auto filter to work properly. Thanks for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time on the MrExcel netcast!

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/


Todays problem involves a complex data set with headings throughout the data. We want to add new columns to the dataset, but it is incredibly tedious to copy the new column headings throughout the report. Episode 546 shows you how to use a combination of AutoFilter, and Visible Cells Only to solve the problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:17) Question about adding new columns to a data set
(00:30) Repeating headings in the data set
(00:44) Solution using auto filter and visible cells only
(02:30) The importance of a blank row between title and headings
(02:44) 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 new columns to Excel data set
ALT semicolon shortcut in Excel
Copying headings in Excel data set
Excel data set with repeating headings
Inserting blank row in Excel
Managing large data sets in Excel
Pasting in Excel using CTRL+V
Removing auto filter in Excel
Repeating headings in Excel
Selecting visible cells only in Excel
Tips for working with headers in Excel
Using auto filter in Excel

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

More from this channel