Excel Tips for Beginners: 15 Essential Skills You Need

Getting Started with Excel

Microsoft Excel is the most widely used spreadsheet tool for data organisation, analysis, and reporting. These 15 skills will make you productive immediately.

1. Basic Arithmetic Formulas

All Excel formulas start with =

• =A1+B1 (add), =A1-B1 (subtract), =A1*B1 (multiply), =A1/B1 (divide)
• =SUM(A1:A10) — sum of a range
• =AVERAGE(A1:A10) — average of a range

2. AutoFill

Type a value in a cell, then drag the small square (fill handle) at the bottom-right corner of the cell to copy it down a column or across a row. Excel detects patterns — it will auto-increment dates, numbers, and sequences.

3. Absolute vs Relative References

• =A1 — relative (changes when you copy to another cell)
• =$A$1 — absolute (stays fixed when copied). Press F4 to toggle.
• =$A1 or =A$1 — mixed reference

4. IF Function

=IF(condition, value_if_true, value_if_false)

Example: =IF(B2>10000, "Above 10K", "Below 10K")

5. VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index, [exact_match])

Example: =VLOOKUP(A2, $D$2:$F$100, 2, 0) — finds A2 in column D, returns value from column E (2nd column).

6. COUNTIF and SUMIF

• =COUNTIF(A:A, "Delhi") — counts cells containing "Delhi"
• =SUMIF(A:A, "Delhi", B:B) — sums column B where column A is "Delhi"

7. Text Functions

• =LEFT(A1, 3) — first 3 characters
• =RIGHT(A1, 4) — last 4 characters
• =MID(A1, 2, 5) — 5 characters starting from position 2
• =LEN(A1) — length of text
• =TRIM(A1) — removes extra spaces
• =UPPER(A1), =LOWER(A1), =PROPER(A1) — change case

8. Date Functions

• =TODAY() — current date
• =DATEDIF(start, end, "Y") — years between dates
• =TEXT(A1, "DD-MM-YYYY") — format date as text

9. Freeze Panes

View → Freeze Panes → Freeze First Row (keeps header visible while scrolling)

10. Filter and Sort

Select data → Data → Filter. Click dropdown arrows on column headers to filter by value.

11. Conditional Formatting

Home → Conditional Formatting → Highlight Cells Rules → Greater Than (highlight cells meeting criteria in colour)

12. Pivot Tables

Insert → PivotTable. Drag fields to Rows, Columns, Values. Instantly summarise large datasets without formulas.

13. Charts

Select data → Insert → Chart. Bar and Line charts suit most business data.

14. Data Validation

Data → Data Validation → Allow: List. Restrict a cell to dropdown values (useful for forms).

15. Keyboard Shortcuts

• Ctrl+C / Ctrl+V — copy/paste
• Ctrl+Z — undo
• Ctrl+Home / Ctrl+End — go to first/last cell
• Ctrl+Shift+End — select to last used cell
• Alt+= — AutoSum
• F2 — edit cell