How long does the Excel PowerPivot course take?

The Excel PowerPivot course lasts one day and is taught in the open learning format. You work independently at the computer with a personal lesson plan, while an experienced trainer guides you at your own pace and level. You learn to set up data models, create relationships, build pivot tables and work with DAX functions. Optionally, attention is also given to Excel with Copilot.

What do you learn in the Excel PowerPivot course?

In the Excel PowerPivot course you learn how to link and analyse data from external sources using pivot tables. You work on a data model, establish relationships between tables, add calculated columns and hierarchies, and build pivot tables with slicers and KPIs. You also get to grips with DAX formulas for in-depth analyses of large datasets.

Do I need prior knowledge of Excel for the PowerPivot course?

Yes, you need advanced knowledge of Excel to take part in the PowerPivot course. You already work with pivot tables and formulas on a regular basis, and you understand how to structure data in a worksheet. Experience with external databases is an advantage, but not a requirement. At the start of the day, the trainer will discuss your starting level with you so that you can get to work in a focused way.

Do I receive a certificate after the Excel PowerPivot course?

Yes, on completion of the Excel PowerPivot course you receive a certificate of participation. This certificate shows that you have attended a full course day on PowerPivot, data models, pivot tables and DAX formulas. You can add it to your CV or LinkedIn profile to demonstrate your advanced Excel skills to your employer or clients.

Can the Excel PowerPivot course also be followed in-company?

Yes, the Excel PowerPivot course can also be taken in-company. You then receive a course on location, tailored to your organisation and to the team's practical examples. This is useful if you want to train several colleagues at once or if you want to work with your own datasets and databases. Contact us for the options and a tailored quotation.

Does the Excel PowerPivot course also cover Copilot for Excel?

Yes, Copilot for Excel is covered optionally during the Excel PowerPivot course. You learn how to use the AI assistant to draw up formulas more quickly, analyse data and recognise patterns in large datasets. Mention at the start of the Open Learning day that you would like to spend time on this, and the trainer will tailor the exercises accordingly.

When should you choose PowerPivot instead of an ordinary pivot table in Excel?

An ordinary PivotTable works perfectly well as long as your data is on a single Excel worksheet and your rows are limited to a few tens of thousands. You choose PowerPivot when you process larger datasets, want to combine data from multiple sources or want to create relationships between tables without using VLOOKUP. You then work with a data model in which tables are linked via key fields, comparable to a relational database. For calculations you use DAX formulas, which are more powerful than regular Excel formulas because they calculate across the entire model. In concrete terms: choose PowerPivot as soon as you pull data from an external database, want to define KPIs or want to reuse measures in multiple PivotTables. For a one-off analysis on a single table, a standard PivotTable remains faster and simpler.

What is the difference between Excel PowerPivot and Power BI for data analysis?

PowerPivot and Power BI use the same underlying technology: the tabular data model and the DAX formula language. The difference lies in the environment and the purpose. PowerPivot runs within Excel and is intended for analysts who already work in Excel, know pivot tables and want to combine large datasets from external sources. Power BI is a separate tool, aimed at interactive dashboards and sharing reports within organisations. You choose PowerPivot if your reporting stays in Excel workbooks, your colleagues work with Excel and you mainly want to use pivot tables and pivot charts. Power BI is a better fit for visually rich dashboards and wider distribution. The DAX knowledge you gain in the Excel PowerPivot course can be applied directly in Power BI.

How does PowerPivot stop Excel from grinding to a halt with large datasets?

PowerPivot uses the VertiPaq engine, an in-memory column database that compresses data heavily. This allows you to analyse millions of rows in Excel, whereas an ordinary worksheet grinds to a halt or becomes slow at around 1,048,576 rows. The data is not stored in cells, but in a separate data model within the Excel file. Calculations run via DAX formulas at column level, which is more efficient than formulas per cell. In practice, this means that a file with 5 million sales records from a database runs smoothly, where a classic pivot table with VLOOKUP would long since have seized up. Important: performance depends on your working memory (at least 8 GB recommended) and on a 64-bit version of Excel. With a 32-bit Excel, the limit remains restricted to a model size of roughly 2 GB.

Which data role is PowerPivot actually intended for, and when does it fall short?

PowerPivot is intended for controllers, financial analysts, business analysts and reporting specialists who work in Excel with data from multiple sources, such as an ERP export, a SQL database and a CSV file. It works well as long as you use your analyses personally or within a small team and your reporting stays in Excel. PowerPivot falls short as soon as you want to publish dashboards centrally for dozens of users, need real-time refreshing from a shared data gateway, or want to set up row-level security per user. In those cases Power BI is the logical next step, often with the same data model and the same DAX formulas. Many participants therefore use PowerPivot as a stepping stone: first build the model in Excel, then move it over to Power BI for wider distribution.

How does PowerPivot relate to DAX, and do I really need to master DAX to get anything out of it?

PowerPivot and DAX are closely intertwined, but they are not the same. PowerPivot is the engine in Excel with which you build a data model: importing tables, creating relationships and making pivot tables on large datasets. DAX (Data Analysis Expressions) is the formula language with which you define calculated columns, measures and KPIs within that model. For basic analyses you can get quite far with just the data model and standard pivot table functions. As soon as you need year-to-date calculations, percentages relative to a total or time intelligence, there is no way around DAX. In the course you get an overview of DAX formulas, the difference with ordinary Excel formulas, the DAX data type and how formulas behave within the relational model. Enough of a basis to write measures independently.

What does Copilot in Excel add to what I do in PowerPivot, and do you cover that as well?

Copilot in Excel is the Microsoft 365 AI assistant that turns natural language into formulas, summaries and charts. Combined with PowerPivot, you can ask Copilot to interpret patterns in your data model, obtain suggestions for DAX calculations or quickly generate a first draft of a PivotTable. PowerPivot remains the engine behind the data model and the relationships; Copilot mainly speeds up the analysis and presentation step on top of that. In this Excel PowerPivot course we optionally pay attention to working with Copilot in Excel. Indicate when booking that you are interested in this, and the trainer will adjust the programme accordingly. For the core of the course (data model, relationships, DAX, PivotTables), Copilot is not a requirement.

Excel PowerPivot course (1 day)

Name: Excel PowerPivot
Price: 525.0 EUR
Availability: InStock
Rating: 7.8 (1 reviews)

With Excel PowerPivot you bring data from external databases straight into your pivot tables. In one day you learn to build data models, create relationships and carry out analyses with DAX. Optionally, you also look at Excel with Microsoft 365 Copilot.

Description

In this Excel PowerPivot course you spend a day working with the PowerPivot functionality in Excel. You learn how to link data from external sources, such as databases, to Excel and how to present this data clearly in PivotTables and PivotCharts.

You will work on setting up a data model, creating relationships between data and adding calculated columns and hierarchies. You will also be introduced to DAX (Data Analysis Expressions), the special formula language that allows you to carry out powerful analyses on large volumes of data. Optionally, the course also covers the use of Microsoft 365 Copilot in Excel, so that you can see how this AI assistant can support your work with data.

The course is delivered by an experienced trainer who will discuss your starting level and learning objectives with you. This allows you to focus on the topics that are most relevant to your own practice. Do you have an example from your own work? Feel free to bring it along, so you can work on it directly during the course.

Topics

Importing data from external sources
Linking data and establishing relationships
Setting up a data model
Adding calculated columns and fields
Building hierarchies
Creating pivot tables and pivot charts
Using slicers for interactive filtering
Setting up measures, KPIs and perspectives
Working with Data Analysis Expressions (DAX)
Differences between DAX and Excel formulas
DAX data types and the relational model
Optional: combining Excel with Microsoft 365 Copilot

Result

After this open learning day you can independently connect to external sources such as databases and import that data into Excel. You build a data model, establish relationships between tables and add calculated columns and hierarchies. With DAX formulas you analyse large datasets and present the results in clear pivot tables and pivot charts with slicers, KPIs and perspectives. Optionally, you can also use Copilot in Excel to draw insights from your data more quickly.

Target audience

This course is intended for Excel users who regularly work with large volumes of data and want to get more out of it. Think of finance staff, data analysts, controllers, business analysts and reporting specialists who want to combine and analyse data from various sources in pivot tables.

You already have experience with Excel and know how to work with formulas and pivot tables. If you would also like an optional introduction to Microsoft 365 Copilot in Excel, this can be covered during the course as well.

Teaching method

You take this course in our open learning format: a full course day on which you work through the course material at your own pace. An experienced trainer is present throughout the day to answer your questions and help you further where needed.

At the start of the day you discuss your starting level and your learning objectives, so that you can focus on the topics that are most relevant to you. You are welcome to bring examples from your own working environment, so that you work on familiar situations straight away.

Prior knowledge

You have experience with Excel and know how to work with formulas, PivotTables and charts. Have you taken the Excel Advanced course or do you have a comparable level? Then you will be able to follow this course without difficulty.

Comments

You take this training course in our open learning format: one course day on which you work through the material at your own pace, with an experienced trainer alongside you for explanation and questions. Feel free to bring an example from your own practice, so you can work directly on situations from your own job. When booking, please indicate which version of Excel you use, so that you receive the right course material. Do you work with Microsoft 365? Then you can optionally also get acquainted with Copilot in Excel.

Course format

This training is offered on the basis of our Open Learning teaching method. This means that you work independently, with active guidance from our trainer, to master the material. That way you can achieve your learning objectives at your own pace, without being held back by fellow participants working faster or slower. The trainer makes sure that you stay on schedule and meet your learning objectives. This form of learning leaves room for a tailored approach. You have the opportunity to bring in examples from your own practice and IT environment, so that your learning is even more specific. more...

In-company

Would you like to organise this training for a whole group or several groups? This can be cost-effective. For example, we can tailor the content entirely to your organisation, and training a whole group is also cheaper.

Are you looking at large numbers of participants or a complete academy? That involves a great deal. Consider planning and communication, for example, or identifying and agreeing the objectives and content of the training. Or differences in level, feedback mechanisms and safeguarding. Fortunately, Learnit has extensive experience in this field (more than 25 years) and we are happy to help you through this process.

Request a quote

Language

The training is given in Dutch as standard. The trainer has a good command of English. English-language course material can be used. With a minimum enrolment of 3 participants, the training can also be given entirely in English.

Number of days:

1 day

Course times:

from 10 am to 5 pm

Study load:

7 hours

Education level:

This training is suitable for all levels.

Exemptions:

Exemptions do not apply to this course.

Preparation time:

No preparation time applies to this course.

Certificate:

At the end of the course, a certificate is issued as proof of participation.

The best training for you

At Learnit you will always find a course that takes you further. From (technical) IT skills to communication or personal leadership. You can also choose the training format that suits you best: a classroom course, a tailor-made course at your own office, an online course or an e-learning. We have the right course and the right format for everyone. Because learning in the way that suits you best delivers the best results.

Moving forward together

Taking a training course at Learnit means learning together. Together with your experienced trainer, together with your course group or together with your colleagues. And whether you take the training at home, at your office or at one of our training locations, we are always available to help you. To share our knowledge with you in a way that is genuinely useful to you. Getting further together through personal attention and understanding each other well; that is what we believe in.

Apply it directly in your work

Learning theory from a book that you can then hardly use in your work? Not at Learnit! Whichever training you take with us, you can put it to use at your workplace straight away. Because of the unique learning objectives you set together with the trainer at the start of the training. Because of the alternation between theory and (interactive) practical assignments. And by bringing the challenges you face in your daily work along to the training.