Most HR and admin work in small and mid-sized offices lives in a spreadsheet: attendance summaries, leave balances, training lists, asset registers, recruitment pipelines, and salary workings. Yet most of those spreadsheets are held together by copy-paste and manual counting.
You do not need to become an Excel expert. You need about ten functions and four habits.
The ten functions worth learning properly
1. VLOOKUP / XLOOKUP — find a value from another list
Used for: pulling an employee’s department or designation into an attendance sheet, matching a payroll list to a master list, checking whether a name exists in another file.
=VLOOKUP(A2, Master!$A$2:$D$500, 3, FALSE)XLOOKUP is available in newer versions and is less fragile:
=XLOOKUP(A2, Master!$A$2:$A$500, Master!$C$2:$C$500, "Not found")The important habit is adding a not found result. A blank cell hides a problem; “Not found” shows it.
2. COUNTIF and COUNTIFS — count with conditions
Used for: how many employees were late more than three times, how many files are pending, how many candidates reached medical stage.
=COUNTIFS(Attendance!$B:$B, $A2, Attendance!$E:$E, "Late")3. SUMIFS — add up with conditions
Used for: total overtime hours per person per month, or total allowance by department.
=SUMIFS(Overtime!$D:$D, Overtime!$B:$B, $A2, Overtime!$C:$C, ">0")4. IF and IFS — apply simple logic
=IF(E2>=26, "Full attendance", "Check")
=IFS(G2>=90, "Excellent", G2>=75, "Good", G2>=60, "Satisfactory", TRUE, "Needs attention")5. AND / OR / NOT — combine conditions
Used for: flagging a record that needs review, such as a contract that is expiring and has no renewal note.
6. TRIM and CLEAN — fix imported data
Copy-pasted data from another system arrives with extra spaces and invisible characters. =TRIM(CLEAN(A2)) fixes most matching errors before they waste an afternoon.
7. TEXT — control how dates and numbers display
=TEXT(A2,"DD-MMM-YYYY")8. TODAY — build expiry flags automatically
=IF(D2="", "No date", IF(D2-TODAY()<=30, "Expiring soon", "Valid"))9. CONCAT / TEXTJOIN — build references
Used for: creating a file name or a reference number from several cells.
=TEXTJOIN("-", TRUE, B2, TEXT(C2,"YYYYMMDD"), D2)10. IFERROR — keep the sheet readable
=IFERROR(VLOOKUP(...), "Check")An error message in a shared sheet stops people from using it. A clear note keeps it working.
Four habits that matter more than functions
Habit 1: One row per record, one header row
Every sheet should have one clean header row at the top, with data starting on row 2. Merged cells, blank spacer rows, and two-line headers break sorting, filtering, and every lookup you will write later.
Habit 2: Raw data separate from reporting
Keep one input sheet where entries are made, and separate report sheets that read from it. If data is entered directly into the report that people read, corrections overwrite history and nobody can trace changes.
Habit 3: Data validation instead of free typing
Set drop-down lists for status, department, leave type, and priority. Free-typed values create “HR”, “H.R.”, “hr”, and “Human Resource” in the same column, and then nothing sums correctly.
Select the column → Data → Data Validation → List.
Habit 4: Conditional formatting for attention, not decoration
Three rules that earn their keep:
- Highlight leave balances below 0 or above the entitlement.
- Highlight dates within 30 days of expiry.
- Highlight rows where a required field is blank.
Keep colours consistent: red for problem, amber for review, green for complete.
A quick structure for an HR workbook
| Sheet | Purpose | Notes |
|---|---|---|
| Employees | One row per employee, with a unique employee ID | The master list |
| Attendance | Daily or monthly entries | Employee ID links to Employees |
| Leave | One row per leave request | Balance calculated by formula |
| Overtime | One row per approved overtime entry | Approval reference recorded |
| Reports | Summaries by month | Pivot tables or SUMIFS |
| Lists | Drop-down values | Departments, statuses, leave types |
The link between all of these is a unique ID, not a name. Two Ram Bahadur Shresthas will eventually appear in your data, and names change.
Keyboard shortcuts that save real time
| Shortcut | Effect |
|---|---|
Ctrl + Shift + L |
Turn filters on or off |
Ctrl + T |
Convert a range into a table (auto-extends formulas) |
Alt + = |
AutoSum |
F2 |
Edit the active cell |
Ctrl + ; |
Insert today’s date |
Ctrl + Shift + + |
Insert cells/rows |
Ctrl + arrow |
Jump to the edge of data |
F4 |
Cycle absolute/relative references while editing a formula |
Ctrl + T alone is worth learning: Excel tables extend formulas automatically and make structured references readable.
Protecting sensitive data in a spreadsheet
- Keep personnel data in a restricted folder, not a general shared drive.
- Use a password on files that contain salary or identity information, and share the password separately.
- Before sending a file, delete the columns you do not need. A “full” export is a data risk.
- When compiling a list for an external party, use a reference number instead of a name wherever possible.
- Keep a working copy and an archive copy, and name them clearly.
Formulas that break without warning
- Text stored as numbers — left-aligned numbers are usually text. Fix with
=VALUE()or by re-entering. - Trailing spaces — lookups fail while the two values look identical.
TRIMfixes it. - Dates as text — if
TODAY()-A2gives a strange result, the date is text. Use Data → Text to Columns → Date. - Hard-coded values inside formulas — replace them with a cell reference so the rule can be changed once.
- Deleted columns — a lookup pointing at a deleted range returns an error everywhere at once.
A resource that saves you building from scratch
I published a free Advanced Task Tracker – Weekly Planner as an Excel workbook with automatic status counts, overdue flags, and a progress chart. It is a working example of the habits above — structured input, formulas that handle blanks, and reporting that updates by itself.
The point of all this
Spreadsheets do not fail because of missing expertise. They fail because of duplicated entry, inconsistent names, merged cells, and totals that cannot be explained six months later. Ten functions and four habits fix most of it, and the time you save shows up in your salary run, not in your technique.
Community discussion
Comments 0
Comments are reviewed before they appear publicly. Be respectful and avoid sharing private information.
No approved comments yet. You can be the first to share a useful thought.
Add a comment
No account or login is needed. Your comment is emailed for moderation and is published only after approval in the CMS.