How to Combine and Repeat Cells in Excel

Excel users may need to combine duplicate rows to consolidate data or repeat cells for dynamic information to stay updated. Fortunately, the program simplifies these processes with a few clicks.

Step-by-step Guide to Combining Information

The consolidate option is one of the most straightforward ways to get combined data results.

1. Set Up Your Merged Cell Table.

Copy and paste your table headers into the area where you want consolidated data to appear. Next, click on the cell underneath your left-most header and choose the data tab.

2. Add Your Function.

Select “consolidate.” Click the one for your task in the drop-down function menu, such as SUM to tally data. Next, highlight the cell ranges to capture in the “reference” field by clicking on the “table” icon. Hit “add” to populate the content automatically.

Verify checked boxes in the final section — if your data table has headers, mark both the “top row” and “left column” buttons. Only check the “left column” box for tables without headers.

Press “OK,” and you’ll have a new table with consolidated values.

Repeating Cells Step-by-step

Suppose you want a bird’s-eye view of employee hours worked per month. Let’s walk through an example of how to repeat data automatically with Power Query to save time.

1. Turn Data into Tables.

Select the column ranges you’ll use and press Ctrl+T to create a table. Name it, then repeat the process with other data columns.

2. Connect the Tables in Power Query.

Select any cell in the dynamic column — months — and navigate to the data tab. Choose “from sheet” to pull in the table data. Next, click “close and load” in the upper left, and check the option “only create connection” to prevent data duplication in the workbook. Press “OK” to return.

Add the “employee” column to Power Query with the same sequence, stopping before the “close and load” step. Now, you’ll add the dynamic data using the ribbon bar shortcut “custom column.” Name the column and navigate to the formula section of the dialogue box. Next to the equal sign, add your first query — months — by typing and selecting it in the pop-up. Click “OK.”

The display will default to show the word “table.” To change this, hit the expand button in the column header and unselect the “use original column name as prefix” box. Change the data type to “text” with the header icon, then use the “close and load to” option to position it in your workbook at the preferred location.

As more months occur and data loads, you can right-click on a cell in the consolidated table and select “refresh.”

Learn More About Professionally Managed IT Services from Agio

We’re making technology easier and safer for our clients with intelligent IT solutions. Our services can help you save money and keep your systems secure.

Learn how by contacting an Agio expert online.