{"id":15105,"date":"2026-06-26T12:32:00","date_gmt":"2026-06-26T11:32:00","guid":{"rendered":"https:\/\/sapphirebusinesstech.com\/en\/?p=15105"},"modified":"2026-10-05T08:02:38","modified_gmt":"2026-10-05T07:02:38","slug":"excel-spreadsheet-for-hr-analysis","status":"publish","type":"post","link":"https:\/\/sapphirebusinesstech.com\/en\/excel\/excel-spreadsheet-for-hr-analysis\/","title":{"rendered":"Excel Spreadsheet for HR: How to Track Leaves, Headcount and Onboarding Like a Pro"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Key takeaways<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A custom Excel spreadsheet for HR can handle leave tracking, headcount and onboarding in one workbook, giving small teams an operational backbone without a large software investment.<\/li>\n\n\n\n<li>In the Leave Tracker, =NETWORKDAYS(D2,E2) counts working days automatically, colour-coded statuses show approvals at a glance, and COUNTIFS summarises approved leave by type.<\/li>\n\n\n\n<li>A Headcount sheet using COUNTIFS and a <strong>PivotTable<\/strong> by department and employment type lets you refresh a headcount report in seconds, and can support a monthly turnover rate.<\/li>\n\n\n\n<li>A reusable onboarding checklist with dropdowns for task owner and completion, plus a COUNTIF progress bar, stops new-hire tasks getting lost across emails and memory.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"559\" src=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-1024x559.png\" alt=\"Custom Excel spreadsheet for HR showing leave tracking dashboard.\" class=\"wp-image-15104\" srcset=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-1024x559.png 1024w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-300x164.png 300w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-768x419.png 768w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-1536x838.png 1536w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel3-2048x1117.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Example of a custom Excel spreadsheet for HR<\/em><\/figcaption><\/figure>\n\n\n\n<p><strong>A custom Excel spreadsheet for HR<\/strong> is a single workbook that handles leave tracking, headcount and onboarding, with sheets for absences, the current workforce and new-hire tasks, giving small teams an operational backbone without a large software investment.<\/p>\n\n\n\n<p>Managing people is one of the most important jobs in any company and also one of the most complex. HR teams deal with dozens of moving parts every single day: who is on leave, how many people are actually on the team, and whether new hires are getting properly onboarded. When none of this is centralized, things fall through the cracks fast, this is where a custom Excel spreadsheet for HR is 100% needed.<\/p>\n\n\n\n<p>The good news is that you can build a custom Excel spreadsheet for HR that handles all three of these tasks efficiently. This tutorial walks you through each one step by step, so even if you have never built a structured spreadsheet before, you will be able to follow along and get results quickly.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Setting Up Your Leave Tracking Sheet<\/h2>\n\n\n\n<p>The first thing most HR professionals need is a reliable way to monitor employee absences. A leave tracking spreadsheet does not need to be complicated to be effective.<\/p>\n\n\n\n<p>Start by creating a new sheet called &#8220;Leave Tracker.&#8221; Set up the following column headers in Row 1:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Employee Name<\/li>\n\n\n\n<li>Department<\/li>\n\n\n\n<li>Leave Type (Vacation \/ Sick \/ Personal)<\/li>\n\n\n\n<li>Start Date<\/li>\n\n\n\n<li>End Date<\/li>\n\n\n\n<li>Total Days<\/li>\n\n\n\n<li>Status (Approved \/ Pending \/ Denied)<\/li>\n<\/ul>\n\n\n\n<p>To calculate total days automatically, use a simple formula in the &#8220;Total Days&#8221; column:<br>=NETWORKDAYS(D2,E2)<\/p>\n\n\n\n<p>This formula counts only working days between the start and end date, which is exactly what you need. Furthermore, you can <a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/conditional-formatting-in-excel\/\">apply conditional formatting<\/a> to the &#8220;Status&#8221; column to color-code each entry; Green for approved, yellow for pending, and red for denied. This makes it immediately clear at a glance where each request stands.<\/p>\n\n\n\n<p>Additionally, you can add a summary table on the side using <em>COUNTIF <\/em>formulas to show how many leaves of each type have been approved in the current month. For example:<br><em>=COUNTIFS(C:C,&#8221;Vacation&#8221;,G:G,&#8221;Approved&#8221;)<\/em><\/p>\n\n\n\n<p>However, keep in mind that as your team grows, this sheet can become difficult to manage manually. That is when a more structured solution starts to make sense.<\/p>\n\n\n\n<div class=\"sbt-cta-box\" style=\"margin:30px 0;padding:22px 26px;background:#eef4ff;border-radius:12px;text-align:center\"><p style=\"margin:0 0 16px;color:#1e293b;font-size:1rem;line-height:1.6\">Is your leave tracker getting harder to manage as the team grows? Tell us how you record absences today and we will talk through a more structured setup.<\/p><p style=\"margin:0\"><a href=\"mailto:contact@sapphirebusinesstech.com?subject=Enquiry%3A%20Excel%20Spreadsheet%20for%20HR%3A%20How%20to%20Track%20Leaves%2C%20Headcount%20and%20Onboarding%20Like%20a%20Pro&amp;body=Hi%21%20I%20read%20the%20article%20%22Excel%20Spreadsheet%20for%20HR%3A%20How%20to%20Track%20Leaves%2C%20Headcount%20and%20Onboarding%20Like%20a%20Pro%22%20on%20the%20Sapphire%20website%20and%20would%20like%20to%20talk%20to%20a%20specialist.\" class=\"sbt-cta\" style=\"display:inline-block;background:#0a2351;color:#fff;border:none;border-radius:8px;padding:13px 26px;font-weight:700;font-size:1rem;cursor:pointer;text-decoration:none\">Talk to a specialist<\/a><\/p><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Building a Headcount Dashboard<\/h2>\n\n\n\n<p>Next, your HR spreadsheet should include a clear picture of your current workforce. <\/p>\n\n\n\n<p><a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/build-an-excel-dashboard\/\">A headcount dashboard answers the basic questions<\/a>: how many people are on the team, by department, and by employment type?<\/p>\n\n\n\n<p>Create a second sheet called &#8220;Headcount.&#8221; Your base table should include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Employee ID<\/li>\n\n\n\n<li>Full Name<\/li>\n\n\n\n<li>Department<\/li>\n\n\n\n<li>Role \/ Title<\/li>\n\n\n\n<li>Employment Type (Full-time \/ Part-time \/ Contractor)<\/li>\n\n\n\n<li>Start Date<\/li>\n\n\n\n<li>Active (Yes \/ No)<\/li>\n<\/ul>\n\n\n\n<p>To count active employees by department, use COUNTIFS:<br><em>=COUNTIFS(C:C,&#8221;Marketing&#8221;,G:G,&#8221;Yes&#8221;)<\/em><\/p>\n\n\n\n<p>Moreover, a <a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/pivot-table-best-practices\/\">pivot table built on top of this data<\/a> will save you enormous time. Select your entire headcount table, go to Insert &gt; PivotTable, and build a summary that shows total headcount by department and employment type. Therefore, when leadership asks for a headcount report, you can refresh the pivot in seconds instead of counting rows manually.<\/p>\n\n\n\n<p>One useful addition is a &#8220;Turnover Rate&#8221; calculation at the top of the sheet. For example, if you track exit dates in a separate column, you can calculate monthly turnover with a simple ratio formula comparing exits to average headcount.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"559\" src=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-1024x559.png\" alt=\"Excel spreadsheet for HR onboarding checklist with task completion tracking.\" class=\"wp-image-15106\" srcset=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-1024x559.png 1024w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-300x164.png 300w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-768x419.png 768w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-1536x838.png 1536w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/06\/Excel4-2048x1117.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><em>Simple Excel spreadsheet for HR with onboarding view.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Creating an Onboarding Checklist Template<\/h2>\n\n\n\n<p>Onboarding is where many companies lose momentum with new hires. When tasks are scattered across emails and memory, things get missed. Consequently, new employees feel lost and disengaged before they even get started.<\/p>\n\n\n\n<p>Your third sheet, call it &#8220;Onboarding Template&#8221; as it should serve as a reusable checklist for every new hire. Build the following columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Employee Name<\/li>\n\n\n\n<li>Task Description<\/li>\n\n\n\n<li>Assigned To (HR \/ Manager \/ IT)<\/li>\n\n\n\n<li>Due Date<\/li>\n\n\n\n<li>Completed (Yes \/ No)<\/li>\n\n\n\n<li>Notes<\/li>\n<\/ul>\n\n\n\n<p>Pre-fill the &#8220;Task Description&#8221; column with your standard onboarding steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Send welcome email<\/li>\n\n\n\n<li>Set up workstation and system access<\/li>\n\n\n\n<li>Complete payroll and benefits forms<\/li>\n\n\n\n<li>Schedule orientation meeting<\/li>\n\n\n\n<li>Assign onboarding buddy<\/li>\n\n\n\n<li>Review company policies and handbook<\/li>\n\n\n\n<li>Complete first 30-day check-in<\/li>\n<\/ol>\n\n\n\n<p>To make this template reusable, use data validation on the &#8220;Assigned To&#8221; and &#8220;Completed&#8221; columns. Go to Data &gt; Data Validation and set up a dropdown list. This ensures clean, consistent data every time.<\/p>\n\n\n\n<p>Finally, you can use a progress bar formula to show how far along each onboarding process is. Use <em>COUNTIF <\/em>to count completed tasks divided by total tasks, then apply a data bar conditional format to visualize completion percentage directly in the cell.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Sheet<\/th><th>What it tracks<\/th><th>Key formula or feature<\/th><\/tr><\/thead><tbody><tr><td>Leave Tracker<\/td><td>Employee, department, leave type, start and end dates, total days and status<\/td><td>=NETWORKDAYS(D2,E2) for working days; COUNTIFS summary by leave type<\/td><\/tr><tr><td>Headcount<\/td><td>Employee ID, name, department, role, employment type, start date and active flag<\/td><td>COUNTIFS by department; PivotTable by department and employment type<\/td><\/tr><tr><td>Onboarding Template<\/td><td>Task, owner, due date, completion and notes for each new hire<\/td><td>Dropdowns through data validation; COUNTIF progress bar with a data bar<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Source and Reference<\/h2>\n\n\n\n<p>For further reading on HR best practices and Excel use in people operations, visit the Society for Human Resource Management (SHRM) at:<a href=\"https:\/\/www.shrm.org\" data-type=\"link\" data-id=\"https:\/\/www.shrm.org\" target=\"_blank\" rel=\"noopener\"> https:\/\/www.shrm.org<\/a><br>SHRM provides extensive guidance on HR systems, compliance, and workforce planning that complements what you can build with Excel.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">A Solid Start Requires the Right Foundation<\/h2>\n\n\n\n<p>Building a custom Excel spreadsheet for HR is a smart and accessible first step for small teams. Leave tracking, headcount management, and onboarding checklists in a single workbook give HR professionals a real operational backbone without a large software investment.<\/p>\n\n\n\n<p>That said, as your team scales, the manual upkeep of spreadsheets can become a bottleneck. Data integrity issues, version control problems, and the risk of human error grow alongside your headcount. At that point, having a <a href=\"https:\/\/sapphirebusinesstech.com\/en\/services-excel-spreadsheets\/\" data-type=\"page\" data-id=\"14440\">professional solution<\/a> built specifically for your company&#8217;s structure and processes. It becomes not just convenient, but necessary.<\/p>\n\n\n\n<p>Sapphire Business Technology has helped more than 500 clients build smarter, more automated <a href=\"https:\/\/sapphirebusinesstech.com\/en\/services-excel\/\" data-type=\"page\" data-id=\"7343\">Excel solutions tailored to their real operational needs<\/a>. If you are ready to take your HR spreadsheet beyond the basics, our team of Excel specialists is here to help you get there.<\/p>\n\n\n\n<div class=\"sbt-cta-box\" style=\"margin:30px 0;padding:22px 26px;background:#eef4ff;border-radius:12px;text-align:center\"><p style=\"margin:0 0 16px;color:#1e293b;font-size:1rem;line-height:1.6\">Is spreadsheet upkeep growing with your headcount? Tell us how your HR data is organised today and what you need to report on.<\/p><p style=\"margin:0\"><a href=\"mailto:contact@sapphirebusinesstech.com?subject=Enquiry%3A%20Excel%20Spreadsheet%20for%20HR%3A%20How%20to%20Track%20Leaves%2C%20Headcount%20and%20Onboarding%20Like%20a%20Pro&amp;body=Hi%21%20I%20read%20the%20article%20%22Excel%20Spreadsheet%20for%20HR%3A%20How%20to%20Track%20Leaves%2C%20Headcount%20and%20Onboarding%20Like%20a%20Pro%22%20on%20the%20Sapphire%20website%20and%20would%20like%20to%20talk%20to%20a%20specialist.\" class=\"sbt-cta\" style=\"display:inline-block;background:#0a2351;color:#fff;border:none;border-radius:8px;padding:13px 26px;font-weight:700;font-size:1rem;cursor:pointer;text-decoration:none\">Email our specialists<\/a><\/p><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently asked questions<\/h2>\n\n\n\n<details class=\"sbt-faq\" open style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">What is a custom Excel spreadsheet for HR?<\/summary><div style=\"padding:2px 0 12px\">It is a single workbook that handles leave tracking, headcount and onboarding for a small team. It gives HR professionals a real operational backbone without a large software investment.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">How do I count leave days automatically in Excel?<\/summary><div style=\"padding:2px 0 12px\">Use =NETWORKDAYS(D2,E2) in the Total Days column, where columns D and E hold the start and end dates. The formula counts only working days between the two dates.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">How do I build a headcount report in Excel?<\/summary><div style=\"padding:2px 0 12px\">Keep a base table with employee ID, name, department, role, employment type, start date and an active flag. Use COUNTIFS to count active employees by department, and a PivotTable to summarise headcount by department and employment type so the report refreshes in seconds.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">What should an onboarding checklist include?<\/summary><div style=\"padding:2px 0 12px\">Columns for employee name, task description, assigned to, due date, completed and notes, pre-filled with standard steps such as the welcome email, system access, payroll forms and a 30-day check-in. Dropdown lists on the Assigned To and Completed columns keep the data consistent.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">When does an HR spreadsheet need a professional solution?<\/summary><div style=\"padding:2px 0 12px\">As the team scales, manual upkeep can become a bottleneck, and data integrity issues, version control problems and the risk of human error grow alongside headcount. At that point a solution built for your company&#8217;s structure and processes becomes necessary.<\/div><\/details>\n\n\n\n<p><em>Content created by Sapphire Business Technology over 500 satisfied clients through Excel, Power BI and others Microsoft 365 softwares.<\/em><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Key takeaways A custom Excel spreadsheet for HR is a single workbook that handles leave tracking, headcount and onboarding, with sheets for absences, the current workforce and new-hire tasks, giving small teams an operational backbone without a large software investment. Managing people is one of the most important jobs in any company and also one [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":15104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[95],"tags":[],"class_list":["post-15105","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel"],"_links":{"self":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/comments?post=15105"}],"version-history":[{"count":4,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15105\/revisions"}],"predecessor-version":[{"id":15636,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15105\/revisions\/15636"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/media\/15104"}],"wp:attachment":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/media?parent=15105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/categories?post=15105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/tags?post=15105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}