Excel BI skills

7 min readNov 22, 2019

These Excel skills are what I learnt in my current position.
Some people may meet the same situation while creating PivotTable and PivotChart, I hope the article will hlep you.

Connect to a data source

Step 1. Data > From Database > From Analysis Services

Step 2. Enter Server name > click Use Windows Authentication > Next

Step 3. select the database > choose a specific cube > Next

The type must be CUBE rather than PERSPECTIVE

Step 4. click Finish

Step 5. select Pivot Table Report

See which data source you are connecting to or Change another data source

Step 1. Click anywhere in your pivot table > Analyze > Change Data Source > Connection Properties

Drag and drop data starts from ∑Value filed to prevent LARGE DATA

建立樞紐分析表時,建議先拖拉資料到∑Values(measure)欄位,可以避免因為資料量過大,excel讀取太久或讀不出來

Create other Pivot Chart and Pivot Table from existing connection

Step 1. Insert > PivotChart > PivotChart or PivotChart & PivotTable

Step 2. select Use an external data source > Choose Connection > select one data source that you’re going to grab data

PivotTable formatting

Step 1. Right-click on PivotTable anywhere > PivotTable Options > click Layout & Format tab , you can setup PivotTable Name and other properties > uncheck Autofit column widths on update to prevent PivotTable columns from resizing on change or refresh.

Step 2. Click Display tab , you can adjust some properties.

I usually uncheck Display filed captions and filter drop downs and check Show items with no data on rows/columns.

You can change PivotTable formatting for Design tab, there are some tabs you can use.

For example, I only want Grand Total for Column.

Step 3. Click Design tab> Grand Totals > On for Columns Only, then the grand total for rows will be hidden.

Hide Row labels and Column labels from PivotTable

Step 1. Right-click on PivotTable any cells > PivotTable Options > Display > uncheck Display filed captions and filter drop downs

Step 2. You can also hide or show header, expand/collapse items within PivotTable for Analyze tab

Format PivotTable Value field

Click Total AorQ > Value Field Settings > Number Format > Number > Decimal places =0 > check Use 1000 Separator(,) >OK

Customize PivotTable value — -值除以1000且顯示小數點後一位

Value Field Settings > Number Format > Custom > Input #,###,.0 in Type > OK

Remove one field from PivotTable permanently

Sometimes, we don’t want a field or more than one fields show on PivotTable based on user requirement. We can remove it permanently.

For instance, I want to remove Total% from the PivotTable

Step 1. Click any cell in the pivot table > Analyze > Fields, Items, & Sets > Create Set Based on Column Items ( Because Total% is in column field)

Step 2. setup Set name > select Total% > click Delete Row > uncheck Replace the fields currently in the column area with the new set > OK

Add Slicers

click any cells in pivot table, from PivotTable Fields, right-click on the field you want it to be a slicer, here I will create a Report Type slicer > right-click on Report Type > Add as Slicer. After that, the Report Type Slicer will show up.

Slicer formatting

Step 1. Right-click on Slicer > Slicer Settings > You can change the Caption of the Slicer

Step2. Right-click on Slicer > Size and Properties > Properties > Select Don’t move or size with cells to stop Slicer from resizing or moving on change or refresh.

Step3. You can also set Slicer formatting from Option tab

Create a PivotChart from a PivotTable

Click any cell in your PivotTable > Insert tab > PivotChart > PivotChart > select Stacked Column

Stop PivotChart from resizing or moving by Slicer

right-click on PivotChart > Format Chart Area> Sizes and Properties > Properties > Don’t move or size with cells

How To Make Row Labels On Same Line In Pivot Table?

After creating a pivot table in Excel, you will see the row labels are listed in only one column. But, if you need to put the row labels on the same line to view the data more intuitively and clearly as following screenshots shown. How could you set the pivot table layout to your need in Excel?

click any cells in PivotTable > Design tab > Report Layout > Show in Tabular Form

How To Repeat Row Labels For Group In PivotTable?

Click any cell in your pivot table > Design tab > Report Layout > Repeat All Item Labels

Repeat Row Labels For Single Field Group In Pivot Table

Except repeating the row labels for the entire pivot table, you can also apply the feature to a specific field in the pivot table only.

Firstly, you need to expand the row labels as outline form as above steps shows, and click one row label which you want to repeat in your pivot table.

For example, I only want to repeat Region :