Excel habits that save an hour a day

In most tax offices, Excel is used like a big calculator with lines on it. Someone types data, scrolls a lot, copies formulas down by hand, and spends the afternoon on work that should take ten minutes. You do not need advanced formulas to fix that. You need a few good habits. This free lesson gives you the five that save the most time, and ten shortcuts worth learning today.

Habit 1: one clean list, not ten sheets

Excel works best when each list follows three rules:

• One row = one item (one invoice, one client, one bank entry).
• One column = one kind of data (date, party, amount). No mixing notes and amounts in one column.
• One header row at the top. No blank rows or merged cells inside the list.

Don't make a new sheet for every month or every client. Keep one list with a "Month" or "Client" column and filter it. Every later lesson in this course (lookups, pivot tables, Power Query) works far better on a clean list like this.

Habit 2: turn every list into a Table (Ctrl+T)

Click anywhere in your list and press Ctrl+T, then tick "My table has headers". You now have an Excel Table. It gives you, for free:

• Filters on every column header.
• Formulas that fill themselves. Type a formula in one row and Excel copies it to the whole column.
• Lists that grow. Add a row at the bottom and it joins the table; totals and pivot tables pick it up.
• A total row (Table Design › Total Row) with sum, count or average from a drop-down.

Give the table a name in Table Design › Table Name, for example FeeRegister. Formulas then read like English: =SUM(FeeRegister[Fee]).

Habit 3: freeze the header, then filter

• View › Freeze Panes › Freeze Top Row keeps the headings visible while you scroll 2,000 rows.
• Filter instead of scrolling. Click the arrow on a header, type part of a name in the search box ("Shah"), and Excel shows only those rows.
• Clear all filters with Data › Clear before you send the file to anyone, so nobody thinks rows are missing.

Habit 4: let Flash Fill do the typing

Flash Fill copies a pattern you show it. Example: column A has names like "SHAH TRADERS - ABCPS1234K" and you want only the PAN in column B.

1. In B2, type the PAN from A2 by hand: ABCPS1234K.
2. Go to B3 and press Ctrl+E.
3. Excel fills the PAN for every row. Check a few rows; if one is wrong, correct it and press Ctrl+E again.

It works for first names, initials, joining "City + PIN", and tidying capital letters. It is in Excel 2013 and later. It is not a formula, so if column A changes later, run it again.

Habit 5: never type the same thing twice

• Drop-down lists (Data › Data Validation › List) for things like status (Pending / Filed / Paid) or staff names. No more "Pendng" and "pending" counted as two things.
• Fill down with Ctrl+D instead of copy-paste.
• Keep a master list of clients (name, PAN, GSTIN, mobile) in one place and look it up everywhere else. Lesson 3 shows how.

10 shortcuts worth learning today

These shortcuts are for Excel on Windows. On a Mac, use the Command key in place of Ctrl for most of them.