Description
Created in just a few clicks, pivot tables are essential for analyzing your data in Excel 2013 or Excel 2016. However, to import your data or format your reports, you often have the feeling of 'tinkering' and you don't don't always get the analysis you want. Based on concrete professional cases, this training day allows you to fully exploit the advanced functionalities of pivot tables and provides effective solutions to recurring problems.
Who is this training for ?
For whom ?
Management controller, financier, analyst, HR manager, anyone required to use and analyze data in Excel 2013 or Excel 2016.
Prerequisites
Training objectives
Training program
- Manage multiple data sources
- Create a connection to an external data file (Access table or query, text file, etc.).
- Get started with the Power Query add-in to import and consolidate data from from different sources.
- Refer to discontinuous cell ranges across multiple sheets or workbooks.
- Relate multiple tables directly in Excel.
- Define dynamically source data to automate updates.
- Leverage advanced features to get the analysis you want
- Use all the possibilities of the summary operator: percentages, accumulations, relative values, rankings, distinct total field.
- Add custom calculations: calculated field and calculated element.
- Use a crosstab value in a calculation formula.
- Dispatch a crosstab on several sheets by filtered element.
- Disconnect crosstabs to make them independent .
- Add graphs to illustrate the analysis.
- Automate report presentation
- Create and use a custom table style.
- Apply formula conditional formatting to highlight data.
- Create custom formats to render explicit numeric values.
- Use very useful tips: Freeze the width of columns.
- Show columns or rows without data.
- Customize the display empty cells.
- Sort according to a custom order.
- Allow or not data extraction.
