MS Excel 2019 - Data Analysis With Pivot Tables and Charts

Discover why pivot tables are a powerful, flexible tool for organizing, summarizing, and analyzing data in Excel 2019, with dynamic views and automated calculations.

Learn how to structure source data for analysis in Excel by organizing data in rectangular form with variables as columns and observations as rows, with clear headers for pivot tables.

Create a pivot table in Excel 2019 by selecting a data range, using the insert tab, and placing it in a new or existing worksheet with optional data model integration.

Navigate the pivot table field list to drag and drop fields into rows, columns, values, and filters, create a pivot table, and analyze data in Excel 2019.

Explore the analyze and design tabs to work with pivot tables: refresh data, adjust fields and layouts, copy pivots, write formulas, display color-coded fields, and create pivot charts.

Develop proficiency in selecting, clearing, moving, and copying pivot tables in Excel 2019, relocate and duplicate pivots across worksheets, and manage fields, filters, and data areas for analysis.

Learn to refresh and update Excel pivot tables as source data changes, adjust the data range, and refresh a single or all pivots via the analyze tab or shortcuts.

Convert the source data to a named table (Excel map) and refresh the pivot table to automatically update as rows and columns change.

Discover how the pivot table cache preserves complete data, enabling continued analysis by clearing the cache, managing source and reference data, and converting the range to a normal table.

Explore how pivot tables detect coordinates across rows, columns, and values to automatically compute sums, averages, and other calculations, and how you drill into complete data by double-clicking.

Master pivot table number formats in Excel, including currency and decimal options, via number format and value field settings. Apply locale-specific formats like U.S. dollar or Canadian currency.

Format empty pivot table cells by using the analyze tab, and the layout and format options to control how blanks appear, enabling clearer data analyses in pivot tables.

Configure pivot table layouts and styles in Excel 2019 using the design tab and table styles, including outline and compact forms, repeating item labels, subtotals, and grand totals.

Explore how pivot tables use a tabular form to structure data and repeat all item labels. Compare compact form with outline view and see how tabular form supports real-time data.

Learn to create custom pivot table styles by applying targeted formatting to headers, totals, and rows, name the style, and add it to the quick access toolbar.

Learn how to customize pivot table headers and labels by renaming fields, adjusting field names, and avoiding duplicate names in a data analysis workflow.

Learn to apply conditional formatting to pivot tables in Excel 2019, using colors, data bars, icons, and rules to highlight values, top items, and patterns, with rule management.

Learn how to add data bars with invisible text in pivot tables using conditional formatting, adjust formatting to prevent overlap, and customize number formats for clear revenue visuals.

Explore advanced conditional formatting in pivot tables, using icons and color scales, and apply rules to specific fields or columns with manage and clear rules.

Create a pivot table from an external data source by connecting to an Excel file, selecting a sheet, and dragging fields into rows, columns, and values.

Learn to sort data in Excel 2019 pivot tables by ascending or descending order, using text, numbers, and simple criteria to organize by columns.

Learn how to fix incorrect alphabetical sorting in pivot tables in Excel 2019 by using a custom list and related pivot options to ensure proper order.

Master filtering data in pivot tables by using label and value filters, selecting required fields, and applying text and numeric criteria such as begins with, contains, equals, and between.

Learn how to apply pivot table label filters with wildcards in excel, using question mark and asterisk to match beginnings and the number of characters.

Learn to enable multiple pivot table filters in excel to filter data by labels starting with a letter and by numeric criteria, such as gross revenue greater than one crore.

Learn to group data in pivot tables by selecting related data, using the group command in the analyze tools, naming groups, and viewing grouped versus complete data.

Learn to group dates and times in pivot tables by quarters, months, and years, using right-click group or the Analyze tab, and adjust automatic date grouping for clean data views.

Learn how pivot table slicers and timelines enable easy filtering of data in Excel, whether in a table or a range, with steps to insert and analyze.

Split pivot table data into separate worksheets by country using the show report filter pages option in the analyze tab, creating a tab for each value.

Learn to aggregate and summarize data in Excel 2019 using pivot tables, applying aggregate functions such as sum, average, max, min, and count, and customize value field settings and formats.

Learn how pivot tables can show count of when text data appears in a column and how to avoid it by clearing nonnumeric text and refreshing.

Shows how to use the show values as option in a pivot table to display revenue data in six ways, including value, average, max, min, and percentages of total.

Learn to display pivot table values as percentages of column total or row total in Excel 2019, using show values as options to analyze data like gross revenue across countries.

Learn to calculate pivot table values as percent of parent, including percent of column, row total, and country-level breakdowns to analyze global data in Excel 2019.

Explore calculating pivot table values using difference from and percentage difference from, compare years 2012 to 2015, and format gross revenue as currency to analyze year-over-year trends.

Learn to compute running totals and percentage totals in pivot tables using show values as running total in, adjust sorting options, and apply custom calculations for revenue data.

Learn to rank pivot table values by largest to smallest or smallest to largest using show values as and right-click options, applying this to gross revenue across years.

Learn how to use the index in pivot table show values, a custom calculation that reveals each cell's relative weight compared to its row and column totals.

Explore the recommended pivot tables to quickly create layouts, then tailor subtotals and grand totals by position, and adjust rows, columns, and blank lines after each item.

Learn to create calculated fields in pivot tables to derive new metrics from existing data, such as profits or return on investment, by applying basic arithmetic and formatting.

Explore pivot table calculations with count columns, convert revenue to currency formats, and create a calculated field to compute average gross revenue per title in different currencies.

Learn pivot table calculations using count columns and calculated fields to compute sums and averages of Facebook likes across titles, such as cast and movie likes.

Explore how pivot table calculated items differ from numeric calculations, and learn when to use grouping or new category columns instead of calculated items to avoid data confusion.

Explore how to generate solve order and list formulas in Excel 2019 data analysis, using calculated items to prioritize calculations, create color-based percentages, and summarize results on a new sheet.

Create pivot tables from data using rows, columns, values, and filters; learn to refresh, adjust layout, format with styles, and connect external data or pivot charts.

Consolidate data from multiple ranges and sheets to create a pivot table for analysis. Learn to use multiple consolidation ranges and build a pivot table in a new worksheet.

Use slicers and a timeline to filter Excel pivot tables. Create slicers for tables or ranges and group dates by months or years.

Learn to write formulas in pivot tables by using calculated fields to sum, average, and combine quarterly data within the analyze tab.

Explore creating and visualizing data with pivot charts, learn dynamic updates as you adjust filters, and use insert, analyze, design, and format tabs to add chart elements and styles.

Learn to create and customize pie and donut charts from pivot tables, duplicate pivot tables, and apply sorting, labeling, and filtering for clear data visualization.

Explore clustered bar charts in a pivot table by selecting data, creating the pivot table, and inserting a bar chart to compare the top five ratings.

Discover how to prevent pivot charts from resizing with cells by choosing 'move but don’t size with cells' and by disabling automatic updates in pivot table options.

Learn to swap rows and columns in charts, switch chart types via analyze and design tabs, and convert tables to bar, column, line, or area charts with stacking and sorting.

Create and customize a stacked area chart from a pivot table in Excel 2019, selecting data, grouping by quarter, and choosing the area chart type for clear multi-year trends.

Explore customizing pivot chart layouts and styles in Excel 2019, adjusting layout, legend, colors, chart styles, and chart elements like axis, axis titles, and data labels.

Move pivot charts to new or existing worksheets using the design tab's move chart option, creating separate sheets such as Excel mapper that keep charts synchronized with pivot tables.

Create slicers and timelines for pivot charts in Excel 2019, connect them to all related charts, and watch all charts update automatically when you filter or adjust the timelines.

Learn to build a dynamic dashboard in Excel 2019 using pivot tables and pivot charts, arrange and format charts, link data updates, and create interactive controls for filters.

Explore the excel 2019 vlookup function: define lookup value, specify the table array and column index number, and choose range_lookup for exact or approximate matches.

Master the Excel 2019 HLOOKUP function to fetch values from the top row of a matrix, understand its four arguments, and distinguish exact versus approximate matches.

Complete data analysis with pivot tables and charts in Excel 2019 and earn certification immediately after finishing the course, with practice files for every section.