Microsoft Excel — complete, step-by-step learning page
1) Spreadsheet interface & elements
A workbook contains worksheets; each sheet is a grid of cells. The active cell’s address appears in the Name Box (you can name ranges there). Enter values and formulas via the Formula Bar. Use Freeze Panes to lock headers, and the Status Bar shows quick Sum/Avg for selections.
Step — Know the building blocks
Workbook is the .xlsx file; sheets live on bottom tabs; columns A,B,C… and rows 1,2,3…; A1 means column A, row 1.
Step — Formula Bar & Name Box
Type values/formulas in the bar; use the Name Box to name ranges like Sales_2025 for readable formulas.
Step — Freeze, zoom, and view
View → Freeze Panes to lock row 1/column A; adjust Zoom; toggle Gridlines/Headings as needed.
2) Open / Import / Close
Step — Create or open
File → New for blank/template; File → Open to open .xlsx/.xls/.csv/.ods; pin frequently used files.
Step — Import CSV correctly
Data → From Text/CSV to pick delimiter/encoding; preview columns as Date/Text to preserve leading zeros or dates.
Step — Protected View
If it opens in Protected View, don’t edit until you trust the source; this guards against malicious content.
Step — Close only the workbook
File → Close shuts the current workbook but keeps Excel running.
3) Cell addressing, printing & saving
3.1 Cell addressing
Step — Relative / Absolute / Mixed
Relative A1 changes when copied; absolute $A$1 stays fixed; mixed A$1/$A1 locks row or column. Press F4 to toggle.
Step — Named ranges
Select a range and type a name in the Name Box (no spaces). Formulas become clear: =SUM(Sales_2025).
3.2 Printing
Step — Set Print Area & Titles
Page Layout → Print Area to restrict print range; Print Titles to repeat headers on each page.
Step — Page setup & scaling
Set Margins/Orientation/Size; use Fit Sheet/All Columns on One Page wisely to keep text readable.
Step — Headers/Footers
Page Setup → Header/Footer to add page numbers, dates, and logos.
3.3 Saving workbooks
Step — Choose the right format
XLSX for editing; XLSM for macros; PDF for fixed sharing; CSV for raw tabular data (single sheet, no formatting).
Step — AutoSave & versions
On OneDrive, turn on AutoSave and use Version History to restore previous states.
4) Enter text, numbers & dates
Step — Text
Leading apostrophe (') forces text (e.g., IDs); Wrap Text for multi-line; Alt+Enter for a new line in a cell.
Step — Numbers
Use Number/Accounting/%/Scientific; custom negative with parentheses: 0.00;[Red](0.00); Data Validation for allowed ranges.
Step — Dates & times
Dates are serial numbers; Ctrl+; inserts today, Ctrl+Shift+: inserts time; use yyyy-mm-dd; ### means column too narrow.
5) Create series (text, numbers, dates)
Step — AutoFill basics
Type a start (Mon/Jan/1/1/2025/1,2) then drag the Fill Handle; use AutoFill menu to choose Series/Copy/Weekdays/Months.
Step — Series dialog
Home → Fill → Series to pick Linear/Growth/Date with Step/Stop; great for 10,20,30… or first day of each month.
Step — Dynamic arrays (365+)
SEQUENCE builds lists without dragging: =SEQUENCE(12,1,1,1); =SEQUENCE(7,1,TODAY(),1) for the next week.
Step — Flash Fill (نصي)
Type an example output in a new column (e.g., full name from first/last), then press Ctrl+E to infer and fill.
6) Formulas & functions
Always start with “=”; use parentheses to control precedence; keep formulas concise and readable with names/tables to reduce errors.
Step — Operators & order
Precedence: () then ^ then * / then + - then comparisons; concatenate with &: =A2&" "&B2.
Step — Core functions
SUM, AVERAGE, MIN, MAX, COUNT/COUNTA, ROUND/ROUNDUP/ROUNDDOWN, IF/IFS, AND/OR/NOT, TEXT, LEFT/RIGHT/MID/LEN, TODAY/NOW/DATE, IFERROR.
Step — Lookups
XLOOKUP is safer than VLOOKUP: =XLOOKUP(lookup, range, return, "N/A", 0). For older versions, use INDEX+MATCH.
Step — Conditional aggregation
SUMIF/COUNTIF for one criterion; SUMIFS/COUNTIFS for multiple; e.g., =SUMIFS(Sales,Region,"East",Month,1).
Step — Relative vs absolute
Lock the reference with F4 (e.g., tax in $E$1): =B2*$E$1; when dragging, the tax stays fixed.
Step — Tables & structured refs
Turn a range into a Table (Ctrl+T); formulas like =SUM(Table1[Amount]) auto-expand with new rows.
- Clearer, less error-prone formulas via names/tables.
- Better maintainability and future scaling.
7) Create charts
Pick chart types by goal: compare categories, show trends, part-to-whole, or variable relationships.
Step — Insert the right chart
Select data including headers; Insert → Recommended Charts or pick Column/Bar/Line/Pie/Scatter/Combo manually.
Step — Adjust data mapping
Design → Switch Row/Column if series are flipped; Select Data to adjust ranges quickly.
Step — Explain the story
Add Chart Element: title, axes, gridlines, data labels; keep colors simple to avoid distraction.
Step — Secondary axis (عند الحاجة)
For different scales, use a Combo chart with a Secondary Axis for just one series; avoid too many axes.
Bonus — essential extras
Tables, Sorting & Filtering
Convert to a Table (Ctrl+T) for built-in filters and quick sorting; filter by slices, sort on multiple columns.
Conditional Formatting
Highlight values automatically (top/bottom, color scales, data bars) with clear, restrained rules.
Data Validation
Enforce dropdowns or numeric bounds to prevent bad input; add input messages and error alerts for clarity.
PivotTables (نظرة سريعة)
Insert → PivotTable to summarize large data; drag fields to Rows/Columns/Values/Filters; Refresh when source changes.
FAQ — common Excel pitfalls
- My formula shows as text.
- Change cell format to General and re-enter “=”; remove leading apostrophe if present.
- I see ### in a cell.
- Widen the column or reduce precision; common with dates/times or large numbers.
- CSV import ruined values/leading zeros.
- Use From Text/CSV and set the column to Text; or apply custom format to keep zeros.
- VLOOKUP returns wrong values.
- Use exact match (last argument FALSE) or switch to XLOOKUP (exact by default).
- Printout is broken across pages.
- Set Print Area, enable Print Titles, adjust Scale to Fit, and preview before printing.
- Excel doesn’t recognize dates.
- Likely locale/formatting; try yyyy-mm-dd or Text to Columns to convert text to dates.
- Slow calculation.
- Reduce volatile functions (NOW/TODAY/INDIRECT), use Tables, and consider Manual + F9 for huge models.
- Chart doesn’t update with new rows.
- Convert the range to a Table for auto-expansion; or use dynamic named ranges.
- Decimal separator mismatch.
- Adjust regional settings or Use system separators; standardize number formats.
Final tips
- Plan headers and units before formulas.
- Turn ranges into Tables (Ctrl+T) so formulas/charts auto-expand.
- Use named ranges instead of cryptic refs.
- Before printing: set Print Area, add Print Titles, adjust Scale.