MS Advance Excel Tricky Formulas To Create Automated Dashboards 36 - sumif & averageif - howtoexcel

Описание: 1. Introduction to Excel

What is Microsoft Excel?

Interface overview: Ribbon, tabs, cells, rows, columns

Workbook vs Worksheet

How to save, open, and close files

Basic navigation & shortcuts



---

2. Basic Excel Operations

Entering and editing data

Copy, paste, cut, and autofill

Formatting cells (font, color, borders, alignment)

Adjusting row height & column width

Merging cells and text wrapping



---

3. Working with Formulas & Functions

Formula basics (=, +, -, *, /)

Relative, absolute, and mixed references

Common functions:

SUM, AVERAGE, MIN, MAX, COUNT, COUNTA

IF, AND, OR, NOT

VLOOKUP, HLOOKUP, XLOOKUP

INDEX + MATCH combination

TEXT, CONCATENATE, LEFT, RIGHT, MID, TRIM




---

4. Data Handling

Sorting and filtering data

Removing duplicates

Data validation and drop-down lists

Flash Fill and AutoFill tricks

Converting text to columns



---

5. Charts and Visualization

Creating charts: Column, Line, Pie, Bar

Customizing chart titles, axes, and legends

Using Sparklines

Conditional formatting for highlights

Data bars, color scales, icon sets



---

6. Tables & Data Analysis Tools

Creating and formatting tables

Table formulas & structured references

Pivot Tables & Pivot Charts

Using slicers and timelines

Grouping and summarizing data



---

7. Advanced Excel Features

Named ranges

Dynamic arrays (SEQUENCE, FILTER, SORT, UNIQUE)

What-If Analysis: Goal Seek, Scenario Manager

Data consolidation

Using Power Query for data cleaning



---

8. Dashboard Creation

Planning a dashboard layout

Using charts, slicers, and pivot tables together

Linking data dynamically

Designing with form controls

Adding interactivity with VBA or formulas



---

9. Excel Automation with VBA

Introduction to the Developer tab

Recording Macros

Writing simple VBA code

Automating repetitive tasks

Creating custom Excel functions



---

10. Practical Projects

Sales dashboard

Attendance tracker

Expense report

Inventory management sheet

Invoice generator



---

11. Tips & Shortcuts

Top 20 keyboard shortcuts

Formatting tricks for presentation

Protecting sheets and files

Printing and page setup

Linking Excel with Word/PowerPoint