Excel Complete
Knowledge Hub

What is Excel?

Microsoft Excel is a software program that arranges data into a grid of rows and columns. The box where a row and a column meet is called a cell. Each cell can hold text, a number, a date, or a formula. The best feature of Excel is that cells can connect to each other. If you change a number in one cell, all the related formulas update automatically. This automatic recalculation makes a spreadsheet much better than a simple table or document.

Who created Excel, and when?

Microsoft created Excel and first released it in 1985 for Apple Macintosh computers. The Windows version came out in 1987. Microsoft built Excel to compete with the most popular spreadsheet tool at that time, Lotus 1-2-3. Soon, Excel became more popular than Lotus 1-2-3 and became the standard tool for businesses, schools, and offices worldwide.

Major version milestones

Why learn Excel

• It is the most common skill asked for in job postings for finance, operations, HR, marketing, and data roles.
• It helps you turn raw data into clear choices (using sums, trends, and totals) without writing hard code.
• It is the first step to learning advanced data analysis tools like Power BI, which uses the same system (Power Query, Power Pivot, DAX).

Career scope

Knowing Excel helps you get jobs like MIS Executive, Data Analyst, Financial Analyst, Business Analyst, Accountant, and Operations Analyst. Advanced Excel skills are also the easiest way to learn Power BI and start a career in Business Intelligence (BI).

Related: Learning Roadmap →

Excel can be used in any field. The same simple grid adapts easily to different kinds of tracking and data analysis. Here is how different industries use it every day:

Before learning formulas, it is important to know the names of the different parts of the screen. This makes tutorials much easier to follow.

Simple Example: In a price sheet, a tax formula like =B2*$E$1 (with tax rate locked in E1) can be dragged down safely. The B2 will change for each row, but $E$1 will stay locked.

• Ctrl + Arrow Key — Instantly jump to the very edge of your data block.
• Ctrl + Home / End — Jump straight to the first cell (A1) or the last used cell of the page.
• Ctrl + F (Find) and Ctrl + H (Replace) — Find specific words/numbers or change them in bulk.
• F5 / Ctrl + G (Go To) — Jump directly to a cell address. The "Go To Special" button helps select only blank cells or cells with formulas.
• Name Box — Type a cell location here and hit Enter to go there instantly.

Excel automatically guesses what kind of data you are typing. It aligns text to the left and numbers to the right by default.

Formatting only changes how data looks on the screen, not its actual value. For example, a cell showing "18%" still stores the value 0.18 inside.

Conditional Formatting

This changes a cell's color automatically based on a rule you set. Example: Go to Home → Conditional Formatting → Highlight Cell Rules → Greater Than to highlight high sales numbers, or use a custom formula like =$D2<$C2 to highlight rows that are under budget.

• Data Bars — Adds a colored bar inside the cell. The bigger the number, the longer the bar.
• Color Scales — Uses color shades (like green to red) to highlight high and low numbers.
• Icon Sets — Adds arrows or traffic lights to show if numbers meet your target thresholds.

Formula basics

Every formula must start with an equals sign (=). Excel calculates the mathematical operators and functions, showing the result in the cell while hiding the formula in the background.

Operator priority (Order of Operations)

1. Parentheses ()
2. Exponents ^
3. Multiplication and Division * / (calculated left to right)
4. Addition and Subtraction + - (calculated left to right)
5. Text joining &
6. Comparisons = < > <= >= <>

Typing =10+2*5 gives an answer of 20 (not 60), because Excel multiplies before adding.

Common mistakes to avoid

• Forgetting to lock a cell reference with $ before dragging a formula down many rows.
• Mismatched parentheses — Missing a closing bracket ) when nesting multiple functions.
• Mixing text strings and numbers without using the & sign, which causes a #VALUE! error.
• Selecting whole columns (like A:A) without a reason, which can make large files run very slowly.

Functions are sorted by categories below. Each entry explains its Purpose, Syntax (how to write it), Arguments, an Example, Output, common Errors, and a practice tip.

Best practice tip: Always start the Y-axis at zero for column and bar charts so you do not distort differences. Make sure to add labels to axes and units.

Select your data cells and press Ctrl + T to turn them into an official Excel Table. Tables expand automatically when you type new rows at the bottom. They use clean structured references like =SUM(Sales[Amount]) instead of standard cell addresses, and update charts or PivotTables instantly.

• Formulas fill down the entire column automatically when a new row is added.
• The main header row stays locked at the top while scrolling, without needing Freeze Panes.
• Includes built-in color styling and a quick Total Row with easy summary math dropdowns.

• Basic Sort: Go to Data → Sort A→Z or Z→A to organize a single column quickly.
• Multi-Level Sort: Go to Data → Sort → Add Level. For example, sort by Region first, then sort by highest Sales inside each region.
• Custom Sort: Sort by a unique order you define (like Small, Medium, Large) instead of basic alphabetical order.
• AutoFilter: The drop-down filter arrows on top of columns used to pick specific items to display.
• Advanced Filter: Filters data in its current place or copies matching rows to a new spot using separate criteria rules. Supports OR logic across columns.

Go to Data → Data Validation to restrict what data users can type into a cell. This stops typos and bad data before it entry time, saving you cleanup work later.

• Drop-Down List: Choose "List", then pick a range of cells or type items separated by commas.
• Date Rules: Choose "Date" and set a minimum or maximum range (like blocking future dates).
• Number Rules: Choose "Whole Number" or "Decimal" to force entries to stay within fixed boundaries.
• Custom Formulas: Use a rule like =COUNTIF($A$2:$A$100,A2)=1 to stop users from entering duplicate values.
• Dependent Drop-Downs: A dynamic system where choices in the second drop-down change automatically based on what you picked in the first one. Built using INDIRECT and named ranges.

A PivotTable summarizes massive lists of data instantly. You drag data fields into four simple zones: Rows, Columns, Values, and Filters. The table updates immediately when your data source changes.

Simple Example: To see total sales sorted by region and quarter, drag Region into Rows, Quarter into Columns, and Sales into Values (set to Sum).

A good Excel dashboard fits onto a single screen view and answers important business questions instantly. Common components include:

• KPI Cards — Large single numbers showing big values (like Total Revenue or conversion rates) along with trend arrows.
• Interactive Charts connected to slicers, so clicking one button updates all graphics at the same time.
• Dynamic Titles that change using formulas, like ="Sales Dashboard – "&TEXT(TODAY(),"mmmm yyyy").

• Goal Seek — Works backward to find inputs: "What sales number do I need to reach my profit goal?"
• Scenario Manager — Lets you save and easily switch between different business assumptions (like Best Case or Worst Case).
• Solver — A built-in add-in tool that finds the best business answers based on fixed limits (like maximizing profit with a fixed team size).
• Forecast Sheet — Creates a quick trend line into the future using historical timeline data.

Power Query (found under Data → Get & Transform) is Excel's data cleaning tool. It connects to a source file, records your step-by-step cleaning edits, and imports the clean data. You can rerun the whole cleanup later with a single click.

• Import: Pull data from CSV files, folders, databases, or public websites.
• Transform: Remove junk columns, split text blocks apart, change data types, or unpivot data.
• Merge: Connect two separate tables together like a repeatable VLOOKUP.
• Append: Stack multiple data tables with matching columns directly on top of each other into one big list.
• Clean: Trim empty spaces, fix uppercase text, and clear out duplicate rows as saved steps you can edit anytime.

Power Pivot links multiple separate data tables together using **relationships** (like a relational database system). This lets you build a single PivotTable that summarizes across tables without merging them with a giant VLOOKUP formula.

Measures are calculated data fields written in **DAX** (Data Analysis Expressions) language, like Total Sales := SUM(Sales[Amount]). They calculate correctly no matter how you filter or slice the PivotTable. This is the exact same engine used inside Power BI.

• Record Macro: (On the Developer tab) Automatically saves your clicks and typing into reusable VBA code scripts.
• Edit VBA: Press Alt + F11 to open the code editor window to tweak recorded actions or write code scripts from scratch.
• Run Macro: Press Alt + F8 or click a custom button to run your automation script.
• Security Info: Macros from the internet can have unsafe code. Excel turns them off by default. Only run macros from people or files you trust.

• Print Area: Go to Page Layout → Print Area → Set to print only your selected cells instead of the whole page.
• Margins & Scaling: Choose "Fit Sheet on One Page" to shrink large tables onto a single piece of paper safely.
• Headers/Footers: Add automatic page numbers, file names, or dates to the top or bottom of every printed sheet.
• Print Titles: Repeats the top header row on every printed page automatically for long tables.

• Protect Sheet: Locks specified cells so other users cannot overwrite formulas, while still letting them sort or filter.
• Protect Workbook: Stops other users from adding new sheets, deleting pages, or rearranging tabs.
• File Passwords: Go to File → Info → Protect Workbook → Encrypt with Password to keep data locked safely.
• Comments/Notes: Add floating note cards to specific cells to add context without changing cell data.
• Co-Authoring: Save files on OneDrive or SharePoint to let multiple teammates edit the sheet at the exact same time.

Beginner

Intermediate

Advanced

Scenario Based

Download sample data files to practice your skills. (These buttons are placeholders for your actual download links.)

Build these end-to-end projects to move from basic formulas to building full workbooks:

• Flash Fill: (Ctrl + E) Excel spots your pattern from one typed example and fills the rest automatically (like splitting full names into first/last columns).
• Double-Click Fill Handle: Double-click the tiny corner box to run a formula all the way down to match the adjacent data rows instantly.
• Ctrl + Enter: Select multiple cells, type data or formula, and hit Ctrl + Enter to fill all of them at the exact same time.
• Alt + Enter: Inserts a clean line break to type text on a new line inside a single cell box.
• Paste Special as Values: Clears out underlying formulas while permanently saving the final calculated numbers inside cells.
• Watch Window: (On the Formulas tab) Keeps a floating view block on an important cell's values even when you scroll away.

Which one to use: Choose Microsoft Excel for deep financial modeling, massive files, or complex data query cleanup tasks. Choose Google Sheets for live online teamwork with multiple users editing from basic browsers instantly.

This FAQ set is intentionally structured to keep growing — add new .acc-item blocks in the same format to reach 100+.

1. Beginner: Screen layout basics, simple formulas, formatting cells, data entry, and sorting/filtering.
2. Intermediate: Advanced lookups, conditional rules (IFS), PivotTable summaries, chart generation, and data validations.
3. Advanced: Power Query tools, dynamic array formulas, interactive dashboards, and macro automation scripts.
4. Expert: Data models in Power Pivot, DAX expressions, complex custom VBA coding, and system model audits.