Skip to content
Related Articles

Related Articles

How to Flatten Data in Excel Pivot Table?

View Discussion
Improve Article
Save Article
  • Last Updated : 05 Jul, 2022

In order to make the format more usable, it’s possible to “flatten” the pivot table in Excel. To do this, click anyplace on the turn table to actuate the PivotTable Tools menu. Click Design, then Report Layout, and then, at that point, Show in Tabular Form. This will isolate the line names and make it simpler to investigate information.

Excel Power Pivot – Flattened

Flatten is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view (for example an inline view that contains a relationship alluding to different tables that go before it in the FROM clause). FLATTEN can convert semi-structured data to a relational representation.

How do you use flatten in Excel?

Google Sheets has a capability called =FLATTEN(), which changes over a reach, or different reaches, into a solitary section. For example, if the following table was in A1:C3…

1 2 3
4 5 6
7 8 9

If you entered =UNIQUE(A1:C3) in A5, you would get the following dynamic range output,

1
2
3
4
5
6
7
8
9

Given this is a dynamic range output, it can then be used in things like UNIQUE, FILTER, SORT, and so on, or referred to in one more regular formula as A5#.

Flatten a pivot table in excel

This is another trick gleaned from investigating information to be imported to a Data Warehouse from an Excel bookkeeping sheet. In some cases, we are given information in a pivot table, which is not the most useful format for exploration or extraction, particularly because all the row labels are in the same column by default.

Pivot-table

 

In order to make the format more usable, it’s possible to “flatten” the pivot table in Excel. To do this, click anyplace on the pivot table to activate the PivotTable Tools menu. Click Design, then Report Layout & then Show in Tabular Form.

Report-layout

 

This will separate out the row labels & make it simpler to explore data.

Row-labels-separated

 

How to Flatten, Repeat, and Fill Labels Down in Excel

Many of Excel’s features, like PivotTables, Charting, AutoFilter, and the Subtotal highlight, were intended to work with level information. Level information is depicted as information that holds values in all cells inside the table. Everything data about the record is gotten from the qualities in the line, and, not from its situation inside the table. It is easy to outwardly see the distinction. Along these lines, this screen capture shows information that is not flat,

Information-not-flat

 

You can see labels are not repeated, and there are cells with missing values. Hence, we should decide on data about a record in view of the place of the line inside the table. For instance, we know that line 39 is for Bayshore Water, at the same time, we just realize that column 40 is for Bayshore Water in view of its situation inside the table. In contrast, flat data contains repeated labels as needed. This screenshot shows flat data,

Flat-data

 

Summary

  1. Select a range that you want to flatten – typically, a column of labels.
  2. Highlight the empty cells only – hit F5 (GoTo) and select Special > Blanks.
  3. Type equals (=) and then the Up Arrow to enter a formula with a direct cell reference to the first data label.
  4. Instead of hitting enter, hold down Control and hit Enter.
  5. To replace the formulas with values, select the entire column, and then Copy/Paste Special > Values

Step 1: First, select the range that you’d like to flatten. This is typically a column of labels you want to repeat, represented by B39:B62 as shown image,

Selecting-range

 

Step 2: Next, we want to select only the empty cells within the range. We can simply use the Go To order for this. 
Hit the F5 key on your keyboard to bring up the Go To dialog, as shown image

Dialog

 

Then, hit the Special button to raise the Go To Special dialog as shown in the image,

Special-dialog

 

Click OK, and Excel will select only the empty/blank cells within the original range, as shown in the image,

Empty-cells-selected

 

Step 3: Now, we need to write a formula that pulls the value from the cell above. This is easily accomplished by typing an equivalent sign (=) and then hitting the Up Arrow key on your keyboard. There are alternate ways, however, to me, this is the simplest method for writing the formula. Now, resist the urge to hit the Enter key. Do Not hit Enter yet. The resulting formula is shown in the image,

Value-pulled

 

Step 4: Now, we need to fill this formula down through all selected (black) cells. This is finished by holding down the Control key, and then pressing Enter immediately after writing the formula. If you have written the formula, and have already pressed Enter, you’ll need to write the formula again, and press Ctrl+Enter instead of entering. The Ctrl+Enter shortcut tells Excel to perform two tasks at once. Enter the formula, and, fill it down through all selected cells. The result of this command is shown image:

Formula-entered

 

Step 5: Now, all that remains is to replace the formulas with their values. First, select the entire section. Excel doesn’t let us perform the next step with multiple ranges selected, so, we need to select a single column range. Now, we just copy the range using any method you prefer (Ribbon, right-click, keyboard shortcut). Then, we do a Paste Special. In the Paste Special dialog box that pops up, we select Values, as shown in the image,

Paste-special

 

When we click OK, we are finished. We repeat these steps on the Account column, flat data as shown in the image:

Flat-data

 

Note: You can typically perform this task on multiple columns at the same time, it only works if the first row has values for all selected columns, so, just be sure to review and doublecheck your work.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!