The PowerPivot Ribbon

power pivot training courses

The PowerPivot Ribbon

Let’s get started with using PowerPivot.

You should have the PowerPivot ribbon loaded in Excel. When you click on it you will see the following.

powerpivot-ribbon-excel-2013

To start your PowerPivot click on the Manage button (called PowerPivot Window in Excel 2010).

Lets work through the ribbons that are available in this new window and get a quick understanding of what the buttons can do. We will cover them in detail at a later stage of the course.

PowerPivot Home Tab

Watch the video clip below or read the extracts lower down

Home Ribbon

powerpivot-ribbon-home-tab

Paste

Paste, Paste Append, Paste Replace, Copy- Used to get items into and out of the clipboard. Key NEW feature is the Paste Append which puts information below an existing table. Will be covered in more detail in the Import data section

Get External Data:

Buttons to import data from other sources and manage these connections. Will be covered in more detail in the Import data section

Refresh

Does what it says and allows you to refresh your tables. Includes the ability to refresh all at once.

Pivot Table

Takes your PowerPivot workings and puts then into various forms of pivot tables or charts in your Excel workbook. Will be covered in detail in other parts of the course.

Formatting

Allows you to change the format of entire columns to suit your reading needs.

SORT AND FILTER

Allows sorting and clearing of filters put onto the table. This works in a similar way to Excel’s sort and filter.

A nice new feature is the ability to be able to sort one column based on the sort order in another column e.g. a column with day of week numbers (1, 2, 3 etc.) is used to sort the day names (otherwise it sorts Fri, Mon, Sat, Sun, Thurs, Tues, Wed)

As an example if we have a column that has the names of the days of the week and we try and sort it, it will look something like this (alphabetic sorting instead of by day of the week).

powerpivot-sort-column-by-another-column

We can solve this by using the Sort by Columns button and associating the sort order of one column (Day of Week Name) with another column (Day of Week Number).

powerpivot-sort-column-by-another-column

When you click on this button you can specify which column must be used to decide the sort order.

powerpivot-sort-column-by-another-column

As a result the sort order of the column will be based on the associated column as shown below. This sort order will flow through to any pivot tables created.

powerpivot-sort-column-by-another-column

FIND

Find the column you are looking for

CALCULATIONS

Autosum allows you to create subtotals at the bottom of tables.

VIEW

Different View options and allows you to see the Diagram view for relationships

PowerPivot Design Tab

Watch the video or read the extracts below

Design Ribbon

powerpivot-ribbon-design-tab

Columns

Allows you to work with columns in the table so that you can add, delete, freeze, and change widths of columns. Works in a very similar way to Excel.

Calculations

Can be used to create calculated columns. Also can decide the calculation option (manual or automatic). This will be covered in detail later in the course

Relationships

Create and manage relationships within your tables. This will be covered in detail later in the course

Table Properties

Make decisions about the properties of the table including changing where it looks to find the data.

Mark as Date Table

One of your tables should be the table on dates and this is used to tell PowerPivot which one it is. This will be covered in detail later in the course

Edit

Undo/ redo options.

PowerPivot Linked Tab

Watch the video or read the extracts below

Linked Table Ribbon

powerpivot-ribbon-linked-tab

This tab only appears when you are clicked in a linked table (explained later in the course). It allows you to update either all linked tables or selected tables and navigate to the tables.

Update mode allows you to control whether it must automatically update any changes in the Excel table or wait for an explicit command. All of this is covered later in the course.

Lesson tags: powerpivot
Back to: PowerPivot Training Online > Starting to use PowerPivot