Mastering Excel Analytics - Excel Data Analytics from A to Z

Learn how to insert and customize Excel charts, including column charts, by selecting data, highlighting months or cities, and adjusting the data range for clear weather visualization.

Master chart creation and formatting in Excel by using chart tools design and format tabs to set elements, apply templates, switch rows/columns, change chart types, and format style and fill.

Explore the format tab to customize charts in excel, adjusting axes, titles, data labels, colors, and chart templates, and to switch rows and columns for different views.

Formatting chart titles in Excel Analytics, use the format panel to customize borders, gradients, textures, patterns, shadows, glow, transparency, size, angle, distance, and text orientation.

Format the chart area by adjusting fill, border, shadow, glow, and effects, then control size, height, width, aspect ratio, and move-and-size-with-cells options to tailor the chart layout.

Explore how to format chart axes in Excel analytics, adjusting x and y axes, axis options, line styles, text direction, alignment, and display units with major and minor scales.

Explore how to format and position the chart legend in Excel analytics, using the combo box or direct selection, and set legend options for precise placement.

Format the plot area and major gridlines, then customize text options in charts, including coloring, shadows, transparency, and vertical text directions.

Learn how to format a series in a bar chart by adjusting the series overlap and gap width to control the distance between bars across cities.

Learn to use a combo chart with primary and secondary axes to show two data series on one chart, and when to use line charts to avoid overlap.

Learn how to create and save custom Excel templates for charts, including applying effects like glow and drop shadows, and reuse them with city data in future charts.

Learn to create and read histograms in Excel to visualize fare distributions, adjust bin ranges and counts, and use overflow and underflow bins for clearer data grouping.

Visualize Alibaba and Facebook stock price tendencies over time with a line chart, add markers, apply linear and polynomial trend lines, assess fit with R-squared, and forecast forward.

Explore scatter plots to visualize the relationship between two data sets, such as temperature and ice cream sales, and identify positive, negative, or no correlation.

Apply a heat map in Excel using conditional formatting color scales to visualize temperatures, assigning blue for low values and red for high values, with customizable colors.

Learn how conditional formatting highlights high and low values in ice cream sales using green color, data bars, color scales, and top item rules to convey insights quickly.

Learn to create pivot tables in Excel using quick insert methods, format as a table for future data expansion, and refresh to include new rows and columns.

Explore analyzing the Titanic passenger list dataset in Excel using pivot tables to examine survival by class, gender, fare, and embarkation port.

Explore pivot tables and pivot charts to analyze how gender and class relate to survival in Excel analytics.

Create a pivot table from the passenger list by selecting a cell and inserting it, then organize data into filters, columns, rows, and values while exploring compact and outline layouts.

Explore pivot tables by working with the four fields—columns, rows, values, and the field list—to analyze data using gender and class, counting and averaging while switching layouts for readability.

Explore using the filter field list to apply values, combine multiple filters, and assemble a comprehensive 'super table' that reveals all desired data insights.

Learn to set the value field in a pivot table, refresh and clear tables, and summarize values by count or average while organizing columns and rows for efficient data analysis.

Master value field settings for count and count numbers in excel analytics, and learn how empty values, errors, and strings affect results; use ctrl+down arrow to locate blanks.

Learn how to use show values as in pivot table calculations to display percentages, differences from a reference or previous value, running totals, and ranking to analyze data.

Explore how to group fields in a pivot table, create groups by doctor titles and majors, and compute average fares for each group.

Mastering Excel analytics shows how to group dates by day, month, and quarter for birthday data, and how to use grouping and ungrouping to control detail for easier exploration.

Learn to use Flash Fill to automatically extract titles from names, create a title column, and analyze pay by title with sorting and descending averages in Excel.

Sort pivot table values by age and by average fare using smallest-to-largest or largest-to-smallest. Explore sorting titles alphabetically and leverage custom lists for dates, like January and December.

Mastering manual filtering in Excel analytics: use select all or pick items, search to include only matches, and understand how current selections shape visible data.

Learn label filtering in excel analytics for numbers, applying equals, not equals, begins with, ends with, contains, between, and wildcard matching to refine rows and columns.

Apply label filtering for strings using equals, not equals, begins with, ends with, and contains; see how numbers are treated as strings and filter between 3 and 51.

Explore how to apply label filters and value filters in Excel, enable multiple filters, and restrict results to top 10 or top 5 values using analyze options, tools, and filters.

Explore value filtering in Excel analytics, applying numeric filters such as greater than, less than, between, and top five items, while distinguishing it from label filtering.

Explore how a slicer extends filters in Excel, learn to insert a slicer, select fields, use multi-select, and clear selections for senior management to filter data easily.

Explore how to use a timeline filter in Excel to select a time range, refine data by birth dates, months, quarters, or years, and narrow the dataset.

Learn to handle empty cell values by displaying zeros and collapse or expand all fields for a unified data view.

Explore how to apply subtotals and grand totals in a table, toggle their position, and control row and column totals to analyze class, age, and fare patterns.

Create and manipulate a pivot table to compare compact and tabular layouts, showing row labels and fields. Use report layout to repeat labels and insert blank rows for reuse.

Explore pivot table style options to format row headers, column headers, and apply banded rows and columns, then select from many pre-formatted pivot table styles for clear data distinction.

Learn to refresh data and change the data source in Excel analytics, analyze updated datasets, and view targeted subsets like the first passengers or top 10 percent.

Learn to select, move, copy, and delete pivot table data in Excel analytics, and move pivot tables between worksheets to manage your analysis.

Mastering Excel analytics: learn to create calculated fields in pivot tables to compute sums and percentages from existing fields, including handling division by zero with an if condition.

Learn how calculated items sum doctor and lady to create new values. Add new items, perform percentages and ratios, and manage division by zero in calculator items.

Learn how self order determines item values and how list formulas shape item creation, including the role of the last formula in multi-item pivot tables.

Explore pivot charts and pivot tables, create a chart from the pivot table, and apply gender and title filters to count and sort survival outcomes using Titanic data.

Add a timeline based on birthdays to drive a pivot chart and filter all linked charts. Associate the timeline with the data table to synchronize filters like a slicer.

Explore how to add a timeline to a pivot chart using birth date data, filter by birthday ranges, and see connected charts update dynamically like a slicer.

Explore pivot chart options in Excel, including chart type changes, design and format tools, and adding titles and styles to visualize pivot table data.

Explore how to analyze survival statistics and age distribution using charts in Excel, applying a Titanic dataset example to reveal where most people fall in age groups.

The lecture analyzes Titanic survival by embarkation port using Excel analytics, tables, and charts to compare average survival rates and reveal trends.

Analyze how fare relates to survival using a pivot table, comparing averages, standard deviations, maxima, and minima between survivors and non-survivors.

Explore how class and gender relate to survival through charts and tables in excel, revealing correlations and enabling one factor at a time analysis.

Master the what-if analysis with scenario manager to create and switch between scenario groups, forecast outcomes, and analyze sales, price, costs, and profit using named ranges and scenario summary reports.

Learn how goal seek, a core what-if analysis tool, finds the input needed to reach a target profit by adjusting price per banana or quantity, and compares with scenario manager.

Explore data table what-if analysis in Excel to model multiple inputs and outputs, compare scenarios with scenario manager, and see how changes affect profit.

Explore the forecast sheet to create stock price forecasts using historical monthly data, showing forecast lines, confidence intervals, seasonality, and methods for handling missing values.

Learn to use solver for what-if analysis by setting an objective to maximize profit under input constraints, configure linear and nonlinear methods, and interpret solver reports.

Mastering Excel analytics from a to z wraps up by thanking learners, congratulating them on becoming analytics experts, and inviting reviews and questions on tools and functions.