Data Analysts Toolbox: Excel, Python, Power BI, PivotTables

Import data from a Microsoft Access database into Excel and transform it with Power Query to remove unnecessary columns, then load it to a pivot table with date filters.

Practice importing text and Access database data into Excel, transform headers and data types with Power Query, and build pivot tables showing totals by department with a date filter.

Clean and prepare data in Excel by spell checking and standardizing case. Remove blanks and duplicates, format numbers, create a named table, and refresh a pivot chart with 2019 data.

Master creating and manipulating pivot tables in Excel using recommended pivot tables and from-scratch setups with a clean sales underscore data table, including date grouping.

Consolidate data from three worksheets into a single pivot table by turning each into a named table, linking them with order ID, and using cross-table fields.

Learn to group text, numeric, and date fields in pivot tables, creating ranges and labeled groups (months, quarters, years) and easily ungroup data when needed.

Consolidate employee info, department info, and job info into a pivot table by linking through primary keys, name the tables meaningfully, and format the salary sum in a tabular pivot.

Explore pivot table formatting with the design ribbon, apply and customize pivot table styles, including banded rows and columns, live previews, and creating your own custom styles.

Master custom number formatting in pivot tables by using value field settings and four-part formats to control positive, negative, zero values, and text, including currency symbols.

Practice building a pivot table from the data, format salaries correctly, and show NRA or a zero for zero values; then apply a custom style to the pivot table.

Learn to use the summarise values by option in pivot tables to display sum, average, and max, rename labels, format numbers, and organize fields with filters.

Explore how to use the show values as options in pivot tables to compare figures, display percentages of grand totals, running totals, percentage difference from previous, and ranks.

Master sorting pivot tables with ascending or descending order, custom lists, and field choices like product name or total gross sales, plus grouping by country.

Master advanced filtering in pivot tables and Excel, using label, value, and date filters, top 10 filters, and multi-filter setups to extract targeted sales insights.

Practice sorting and filtering a pivot table with HR data by enabling multiple filters, filtering names starting with a, and salaries between 70000 and 100000, sort from largest to smallest.

Insert and format a timeline slicer to filter pivot table data by date, ensuring date fields are properly formatted. Customize timeline options to view months, quarters, or days.

Learn to link slicers to multiple pivot tables, using report connections to control several tables, name pivots clearly, and selectively connect only the slices you need.

Explore calculated fields in pivot tables using the if function to apply a 20% tax on sales above one million, and build a grand total with new fields.

Discover how to create calculated items in pivot tables by selecting category items, like cameras and memory cards, and compare them to calculated fields.

Explore how solve order prioritizes calculations in pivot tables and how calculated items and fields interact, with a hands-on example of percentage returns by region.

Learn how to use the get pivot data formula to extract pivot table totals in Excel and keep results dynamic when the pivot layout changes by converting to cell references.

Master advanced pivot table calculations in Excel by creating a bonus calculated field and a salary with bonus field, then using get pivot data to populate a separate table.

Learn how pivot charts visualize pivot table data and create them from pivot tables. Choose appropriate chart types and manage data layout and readability.

Format pivot charts with granular styling—adjust plot area colors, borders, data series colors, grid lines, and axis options; add a slice to interactively update visuals.

Master dynamic chart titles in Excel by linking pivot charts to slicers and using text join or concat with helper worksheets to reflect selected stores.

Explore how to insert and format sparklines in Excel pivot tables to visualize trend data, including line and column spark lines, with top 10 filters and dashboard-ready visuals.

Practice creating dynamic pivot chart labels with a slicer tied to job type, showing contract or permanent, using a helper sheet to build a flexible title and subtitle.