Accounting & Business Software

Work with cells, ranges and formulas in Microsoft Excel, use functions such as SUM, IF, VLOOKUP and XLOOKUP, and sort, filter, chart and print your data.

• 7 lessons
• About 27 min of reading
• 35 practice questions
• 25 key terms

1. 1Getting Started with Excel5 key points · 4 min read

   • A workbook (.xlsx) contains worksheets made of columns, rows and cells.
   • A cell address is the column letter followed by the row number, such as B4.
   • A range such as A1:C5 uses a colon to mean "from A1 to C5".
   • Text aligns left and numbers align right by default; ##### means the column is too narrow.
   • The fill handle continues series such as Jan, Feb, Mar.
2. 2Formatting Cells and Worksheets5 key points · 3 min read

   • Formatting changes how a value looks, not the value itself.
   • Ctrl+1 opens Format Cells, where you can choose formats such as Currency with the ₹ symbol.
   • Gridlines do not print by default; use Borders for tables you will print.
   • Wrap Text, Merge & Center and AutoFit help data fit neatly.
   • Conditional Formatting changes a cell's look automatically based on its value.
3. 3Formulas and Basic Functions5 key points · 4 min read

   • Every formula starts with =; use cell references so results update automatically.
   • Excel calculates brackets first, then powers, then * and /, then + and -.
   • COUNT counts cells with numbers, while COUNTA counts cells that are not empty.
   • AVERAGE ignores empty cells but includes zeros.
   • Alt+= inserts AutoSum; errors such as #DIV/0!, #NAME? and #REF! point to the cause of a problem.
4. 4Relative, Absolute and Mixed References5 key points · 4 min read

   • Relative references (A1) change when a formula is copied; this is the default.
   • Absolute references ($A$1) stay fixed wherever the formula is copied.
   • Mixed references ($A1 or A$1) lock only the column or only the row.
   • F4 cycles through the reference types while you edit a formula.
   • Use an absolute reference or a named cell for one value, such as a rate, used by many formulas.
5. 5Decisions and Lookups: IF, VLOOKUP and XLOOKUP4 key points · 4 min read

   • IF(test, value_if_true, value_if_false) returns one of two results; text results go in double quotes.
   • VLOOKUP searches the first column of a table and returns a value from a column to its right.
   • Use FALSE as VLOOKUP's last argument for an exact match; a value that is not found gives #N/A.
   • XLOOKUP needs Microsoft 365 or Excel 2021 and later; it matches exactly by default and can return from any column.
6. 6Sorting and Filtering Data5 key points · 4 min read

   • Keep one heading row and no blank rows inside a list you want to sort or filter.
   • Sort A to Z or Z to A sorts whole rows; Data > Sort sorts by several columns.
   • Filter (Ctrl+Shift+L) hides rows that do not match, without deleting them.
   • Ctrl+T turns a list into an Excel Table with automatic filters and formatting.
   • Freeze Top Row keeps the headings visible while you scroll.
7. 7Charts and Printing5 key points · 4 min read

   • Select the data with its headings and use Insert > Recommended Charts or a chart type.
   • Column charts compare categories, line charts show change over time and pie charts show parts of a whole.
   • The Chart Design and Format tabs add titles, labels and styles; charts update when the data changes.
   • Fit Sheet on One Page, Print Area and Print Titles control what prints and how.
   • Gridlines do not print unless you tick Gridlines > Print.