How to Create a Dynamic Excel Chart of Employees Hired by Month
Question details
The user needs to build a dynamic workbook to count and visualize the number of employees hired each month without manually adding new formulas every year.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating an ongoing employee retention and hiring tracker that automatically updates totals in a bar chart when new data is entered.
- Observed behavior
- The goal is to have the chart and data table expand automatically and group hire dates by month and year seamlessly.
Ensure your raw data includes at least two columns: Employee Name (or ID) and Hire Date (or Orientation Date). Verify there are no completely blank rows within your dataset.
Use an Excel Table and PivotTable
Converting your data into an Excel Table and summarizing it with a PivotTable is the most efficient way to track monthly hires dynamically.
By formatting your source data as a Table, Excel automatically expands the data range when you add new employees. The PivotTable can then group these dates by month and year without needing complex, easily broken formulas.
Select your entire dataset, including headers, and press Ctrl+T. Ensure 'My table has headers' is checked and click OK.
Go to the Insert tab on the ribbon and select PivotTable. Choose to place the PivotTable on a New Worksheet and click OK.
Drag the 'Hire Date' (or Orientation Date) field into the Rows area. Excel often groups dates automatically; if not, right-click any date in the PivotTable, select Group, and choose Months and Years. Then, drag 'Employee Name' to the Values area to get a Count.
Click anywhere inside the PivotTable, navigate to the Insert tab, and select a Bar or Column Chart. This chart is now dynamically linked to your summarized data.
When you add new employee rows to your original Table, simply go to the PivotTable Analyze tab and click Refresh. The chart will update automatically with the new months and totals.
Calculate Employee Retention Percentages
If you also need to track whether someone is still employed after 30, 60, or 90 days, use simple COUNTIF and COUNTA functions to generate dynamic percentages.
Track Employee Data Dynamically with WPS Spreadsheet
WPS Spreadsheet offers powerful data analysis tools, including dynamic Tables, PivotTables, and advanced charting, allowing you to easily track monthly hiring metrics and employee retention.
- 1. Import Data: Open your employee hiring data in WPS Spreadsheet.
- 2. Create a Dynamic Table: Highlight your data range and go to Insert > Table so future additions are included automatically.
- 3. Build the PivotTable: Go to Insert > PivotTable to instantly summarize and group your hire dates by month and year.
- 4. Generate the Chart: Insert a Bar Chart from the PivotTable to visually track your employee onboarding trends dynamically.
Frequently Asked Questions
Why is my PivotChart not updating when I add new employees?
If your source data is not formatted as an Excel Table, the PivotTable won't automatically expand its range to capture new data. Format your data as a Table (Ctrl+T) first, and remember to click 'Refresh' on the PivotTable Analyze tab after entering new records.
How do I group dates by month and year in a PivotTable?
Right-click any date value in the PivotTable's Row Labels column, select 'Group', and then choose both 'Months' and 'Years' from the grouping dialogue box. This prevents months from different years from being merged together.
How can I exclude blank rows from my COUNTIF retention percentage?
To ensure an accurate denominator, make sure your COUNTA range only covers cells with valid employee data. Alternatively, use COUNTIFS to add criteria that explicitly exclude blanks, dividing only by rows that contain valid orientation dates.
Can I use a regular chart instead of a PivotChart for employee totals?
Yes, you can insert a standard chart referencing the PivotTable data. However, a PivotChart is recommended because it provides built-in filtering controls directly on the chart, making it much easier to analyze specific months or years interactively.
