Data Analysis Using Excel

Compute totals for student marks with summation formula in Excel, using sum or auto sum; note that formulas start with = and text can be prefixed with a single quote.

Learn how to turn data into a named Excel table, assign meaningful column names like marks in physics, chemistry, and mathematics, and use sum, average, min, and max formulas.

Create nested logical functions in Excel to assign grades based on total marks: fail under 100, second class 100–200, and first class above 200, using and conditions.

Learn to calculate calendar days and working days between two dates using Excel's days and network days functions, excluding weekends and holidays with a holidays table.

Explore how Excel categorizes functions to help you pick the right one for text, numbers, and analytics, using insert suggestions and examples like trim, exact, concat, and standard deviation.

Learn to generate unique email IDs in Excel by trimming spaces, counting duplicates with CountIf, and using dynamic ranges and Index to append occurrence numbers.

Learn to use Excel's name manager to name cells like principal, number of periods, and rate, making formulae such as interest and amount easier to read.

Use Excel's show formulas in formula auditing to reveal formulas, illustrated by a compound interest example. Learn manual calculation with calculate sheet or workbook, and revert to automatic for performance.

Explore how the watch window in Excel helps debug formulas by showing how changing a rate in one cell affects another cell, without scrolling.

Create a simple chart from factory output data in Excel, using a line chart to visualize monthly units of chairs, tables, beds, and cupboards, with chart selection, preview, and resizing.

Explore how to format charts in Excel using chart tools, including chart design and format panes, and options for titles, plot area, and data labels.

Explore formatting axes in charts by toggling the horizontal and vertical axes and adjusting axis options. Customize labels, intervals, display units, and axis titles to refine chart readability.

Format charts in Excel by toggling the chart title, adding data labels, and adjusting label options; decide whether to include a data table.

Explore Excel chart formatting by turning gridlines on or off, viewing minor gridlines, and adjusting legend appearance; changing a data label color updates both the label and the legend.

Explore applying Excel chart presets and themes, remove sums from data, and switch chart types—from clustered column charts and bars to combo charts with a secondary axis for trends.

Add error bars showing 5% ranges, standard deviation, or fixed values, and apply trend lines, including exponential or moving average, with drop lines to highlight values in Excel.

Learn how to select data and choose the right chart in Excel, recognizing pie chart limitations. Explore how recommended charts adapt to data, and preview pivots for multi-dimensional analysis.

Create and analyze pivot tables in Excel using manufacturing data to summarize state-wise total and average outputs, with region mapping via Vlookup.

Create hierarchical pivot table views by year, quarter, and month, grouping date data to compare averages across states and factories with optional regional totals.

Discover how to filter data in Excel, using row and column filters and pivot table filters to view specific records, such as Gujarat, Beijing, and 2020–2022 data.

Learn to create calculated fields in Excel pivot tables by naming a new field office furniture and defining it as chairs plus tables, enabling formulas inside the pivot tables.

Explore how pivot tables compare factory outputs across regions, years, and factories by applying show values as options, percentages, running totals, and ranking to reveal hierarchical insights.

Explore individual data points with pivot tables by double-clicking cells to drill down into details, such as factory, region, and time, enabling deeper analysis beyond country-level summaries.

Learn to use slicers in Excel to filter data visually across all tables, instantly reflecting region selections (East, South, North, West) and reducing clicks compared to standard filters.

Master pivot table formatting in Excel, covering header styles, subtotals and totals, and report layouts such as compact, outline, and tabular, with options to repeat item labels.

Create pivot charts from pivot tables to visually analyze data trends, placing timeline on the x-axis, factories in the legend, and region filters to compare Maharashtra across years.

Explore how sparklines visualize positive and negative data using win-loss charts, showing values above or below the axis with constant bar height, whereas column charts vary with value.

Explore creating headers and footers in Excel to add logos and confidentiality notes across pages, using insert header and footer, header/footer tools, presets, and the left, center, and right sections.

Explore fixed header and footer formats in Excel, customize page numbers and text, and apply different first-page headers to tailor reports from title to subsequent pages.

Learn to forecast values in Excel by selecting data and using the forecast sheet to plot blue historical data against brown forecast lines, with upper and lower bounds.

Explore forecasting in Excel by adjusting the end date to December, enabling a confidence interval, and interpreting the upper and lower bounds, with seasonality detection and forecast statistics.

Master what-if analysis with the scenario manager by creating best, realistic, and pessimistic cases, viewing scenario values, and generating a scenario summary report for dashboard-based comparative analysis.

Apply the solver add-in to minimize total cost while meeting a target profit by adjusting units to manufacture under integer constraints for chairs, tables, and beds.

Explore a dashboard that compares targets versus actuals for chairs, tables, and beds, using year and region slicers to update variance reports and manufacturing output.

Create pivot tables with regions as rows and years as columns to sum shares, tables, and beds, then build a pivot chart and dashboard to compare target and actual totals.

Insert slicers for region and year, link them to the pivot table and pivot chart via report connections on the dashboard, and use a single slicer to control both pivots.

Master building dashboards in Excel by using get pivot data to fetch pivot table fields, apply region and year filters, and join outputs with text join for titles.