How to Flatten Data in Excel 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 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.

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.

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

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,

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,

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,

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

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

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

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,

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:

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,

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

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.