Microsoft Excel- Create Dashboards and Master Power Query

Explore essential date and time functions in Excel, including today, now, year, month, date, hour, minute, second, week and weekday, plus formatting, time zone customization, and days between dates.

fix the problematic date format using text to columns in the data tab, selecting delimited to illustrate, then set the date format to month day year.

Learn to add a price column with VLOOKUP to match product IDs to prices from the products worksheet, using exact match and the leftmost column requirement.

Apply the Excel match function to identify customers who returned orders by exact matching the customer IDs against the returns sheet, and use IFNA for clean results.

Learn to count returns in Excel using countif for a single criterion and countifs for two criteria, such as number equals ten and country equals Brazil.

Remove duplicates from your table by using Excel's data tab remove duplicates feature to keep only unique values, such as country names, while preserving the first sighting of each.

Explore the Power Query editor home tab to manage queries with close and load, refresh preview, apply steps, adjust data types, split columns, and transform data for dashboards in Excel.

Explore the Power Query Editor Transform tab, applying transpose, rename, replace values, and mathematical operations to count rows and compute sums, averages, and more.

Power Query Editor's add column tab creates a new column with conditional logic, offering add conditional column and custom column options, and allowing set values or other columns.

Clean and transform data in the Power Query Editor by removing empty product IDs, filtering nulls, and converting price to currency, then reuse applied steps and close and load.

Use power query editor to clean the orders query. Correct date formats with locale and convert date and time columns to proper data types, removing the time component.

Merge orders and products in Power Query Editor using product ID as key with a left outer join. Expand the result to include price and category in an analysis-ready file.

Add a custom column in Power Query Editor to show the month name for each order using date.months and the order date, verifying syntax and loading results.

Create an on-time column in Power Query Editor by comparing delivery date to due date, using a conditional column that outputs 0 for late and 1 for on time.

Learn to add a 'days to supply' column in Power Query Editor by subtracting order date from delivery date using duration.days, review applied steps, and load the dataset.

Explore how to manage data connections in Excel, including refresh all, enabled background refresh, refresh intervals, and fast load, to optimize dashboard updates for orders and products data.

Enable fast data load in Excel by adjusting default settings: go to data, get data options, load tab, and check fast data load to dramatically speed up data refresh.

Sort your data table by the months of the year in the correct order using Excel's data tab and the default months list.

Explore pivot tables, pivot charts, slicers, and timelines to build a dynamic dashboard, and learn how to connect elements using report connections.

Build pivot tables from an Excel table, place them in a worksheet, and use rows, columns, values, and filters to analyze book quantities by location and distributor to power dashboards.

Create a pivot table from the orders data in a worksheet, rename to a one word table name, and use the pivot table analyze tab for refresh data.

Create pivot charts alongside pivot tables in Excel, setting distributor as the axis and quantity as values, then rename, refresh data, and choose chart types with data labels.

Apply the slicer tool to dynamically filter pivot tables and pivot charts, and connect it to multiple charts using the report connections window for a cohesive Excel dashboard.

Build a pivot table and pivot chart, add category and distributor, and set days to supply and price to average in United States dollar, using the timeline to filter dates.

create a pivot table to show the sum of quantity and total income, format as a whole-number with comma separators, and refresh the data to match the dashboard design.

Create a pivot table with an on time percentage measure in Excel, using a data model filter to show on time orders (over $1,000) on a dynamic dashboard.

Create a pivot table to show the average days to supply for each product, customize the values to average with two decimal places, and format the dashboard for clarity.

Add a pivot chart to the dashboard from the orders table, plot sum of price by category, format as US dollar currency, refine legend and labels, and resize for clarity.

Create a quantity per category pivot chart for a dashboard by placing total quantity in values and category in axis, formatting numbers, and sorting categories descending with data labels.

Build a pivot chart to show quantity per location by placing quantity in values and location in the axis, then rename, refresh, and customize the area chart on the dashboard.

Create a pivot chart showing the average days to supply per category, then add data labels, sort by average days to supply in ascending order, and finalize formatting.

Create a pivot chart showing the on time percentage per distributor, with on time percentage in values, distributor in the axis, and format as a horizontal bar.

Create a filterable dashboard using a category slicer to connect all pivot charts, changing visuals dynamically when selecting a category.

Add a second month slicer to the dashboard, connect it to all month-related pivot charts, and keep the money per month pivot chart static while other visuals respond to month.

Learn how to add a distributor slicer to a dashboard, filter data per distributor, connect it to all relevant pivot charts, and use multiple slicers for precise insights.

Add a location slicer to the dashboard, arrange 12 location columns, and connect it to all pivot charts and tables for a dynamic, filtered view.

Customize your dashboard by turning off gridlines, applying themes from the page layout, and downloading a theme from the internet to create a professional, sleek look with a black background.

Customize each graph individually by removing the fill and applying a blue outline, achieving cleaner, sleeker dashboards.

Design customized slicers in Excel by creating a new slicer style, naming it my design, and applying colors for header, selected items, and hover states to craft dashboards.

Fine-tune your Excel dashboard in view mode by removing headlines and the formula bar, then zoom to the optimal viewpoint for a professional presentation.

Master the save as workflow in Excel by choosing a location, naming the file, and selecting Excel workbook or Excel macro-enabled workbook to preserve a dynamic dashboard.