Spreadsheet Planning

9 Excel Hacks You Should be Doing in Spreadsheet-based Workforce Planning

Lara Klinkenberg 5 min read Download as PDF
9 Excel Hacks You Should be Doing in Spreadsheet-based Workforce Planning

Workforce planning with Excel or other spreadsheet tools can be a real challenge at times. You feel frustrated when things don’t work the way you’d expect them to. You may also experience limitations, errors, and complexities that cost you time and effort. But, your spreadsheet can still be a powerful tool that allows you to do your daily job as a resource planner. After speaking to WFM experts and planners, we have collected some basic and advanced Excel Hacks that will support you in spreadsheet-based planning.

No time to read the full article? Just download it as a PDF and read it offline, anytime, anywhere.

General tips and tricks for working with Excel

Let’s get started with a few general tips and tricks. 

1. Take the shortcut, whenever you can  

You can save a lot of time by avoiding multiple clicks and using shortcuts instead. We’ve put together a few of the most common shortcuts that should come in handy in your daily routine:

  • CTRL-Z (Windows) or CMD-Z (MAC): We especially love this one because it’s the easiest way to undo the last action you applied to your spreadsheet.
  • CTRL-SHIFT-Up/Down-Arrow (Windows) or CMD-SHIFT-Up/Down-Arrow (MAC): This command is a shortcut to select all the cells above or below a cell. Killer fact: When applying this shortcut, you won’t select any empty cells which is great when working with a large data set.
  • SHIFT-F11 (Windows) or Shift-fn-F11: Save 3 clicks and create a new worksheet
  • Alt= (Windows) or CMD-Shift-T (Mac): This nice shortcut lets you auto-sum a range of selected cells. 
  • CTRL-Up-Arrow Once (Windows) or CMD-Up-Arrow Once (Mac): Scroll straight to the last line of data that appears before a blank row. Use CTRL-Up-Arrow Twice (Windows) or CMD-Up-Arrow Twice (Mac) to quickly move back to the top of your spreadsheet - not even Usain Bolt will be able to keep up with you ;)

2. Structure is everything

Some of the most common mistakes while working with spreadsheets are structural. The result: you're most likely 1) spending too much time getting the correct data into the right place or 2) searching through numerous worksheets and tables. To avoid these typical mistakes, make sure not to: 

  • Spread your data over multiple different spreadsheets and worksheets
  • Create and duplicate sheets and tables without clear naming conventions
  • Leave blank columns and rows as well as cells in data tables

3. Get up-to-speed

Referencing entire columns instead of just the cells you need slows down your spreadsheet to snail speed. It causes Excel to examine thousands, if not millions, of cells it would otherwise ignore. 

Additionally, a result of your spreadsheets growing with more and more data is formulas becoming clunky or even breaking. Rather than constantly readjusting existing formulas in large sheets, use Lookup formulas to extract only the information you need.

4. Better safe than sorry

    • Always keep records of changes with a 'version' tab  - just in case Management asks for formulas to be amended. 
    • Maintain a glossary of calculations and assumptions, and document their source to ensure complete transparency.
    • When creating reports or Dashboards, set up a data tab linked to a formula/calculations tab and a separate charts tab. This allows you to protect tabs and reduce the risk of  errors and formulas being accidentally overwritten.
    • Remember to use 3-D references. It helps when referencing cell ranges on multiple worksheets! 

Excel hacks to consider in contact center workforce planning

Now that we’ve covered some of the basics, here are a few more advanced Excel hacks to support you in workforce planning. 

5. Sometimes 24 hours just ain’t enough

  • Make sure to use the [H]:MM:SS time format. This allows you to count above 24 hours. 

When you’re running certain tasks frequently, let’s say accessing pivot tables, you want to make sure you’re not wasting time. A time saving tip is to use ribbon shortcuts.

6. A picture is worth 1000 words

  • Proud of your forecasting table? Well, as much of an effort you might have put into it, it’s not going to get the recognition it deserves if no one else understands what you’re saying. Make sure to show forecasts in a graphical format. It’s usually easier for everyone to grasp what you mean.

7. Keep it simple 

  • How many columns is too much? There’s no exact numerical answer but operate under the principle the less, the better. One way to avoid adding extra columns to your data is to use calculated fields in pivot tables.
  • Where there’s data in the source file, make sure to select whole columns – not just the cells.
  • Use the WEEKNUM function in Excel to incorporate your previous years’ data with the recent data to determine WOM (Week of Month) and DOW (Day of Week) distribution.

8. Master the holiday struggle

  • Create a holiday factors tab that correlates volumes on particular dates vs normal seasonal volumes – and recognize this for the next date in the series. You can then give lift or reduction based on the previous volumes of that set only.
  • We can’t stress this enough. Make sure to use the following workday formula in Excel for public holidays – =WORKDAY(Date,NumberOfDays,PublicHolidayList)
  • Want to predict the future? Instead of a standard % shrinkage, use Non-Business-As-Usual activity. Apply a variable shrinkage forecast based on planned activity and known holiday bookings for the coming months.

9. Don’t cut corners


Apart from knowing what’s going on in your business, be sure to consider external environmental factors to make informed decisions. You want to consider not only seasonality, marketing campaigns and trends but also events that correlate with contact volume. Depending on the industry you are in, these can be weather forecasts, traffic situations, political decisions, and whatever can have an impact on your workload forecast. 

  • Tip: For long-term forecasting, revert to exponential smoothing and weighted average combined.  Exponential smoothing is not really good for long term.

Conclusion

These are just a few tips that we and our network found useful and worth sharing. Of course there’s more to it. Try these different tips and tricks or tell us about your favorite Excel hack. 

For those who are less experienced in Excel, it’s always helpful to take part in an advanced Excel course or simply ask your more experienced colleagues for advice. Tap the number of communities and social media groups to exchange Excel hacks and learn from each other. Altogether, we’re confident this will have you well on your way to becoming an Excel expert!

For more exciting insights on how to level up your planning with Excel and turn your spreadsheet into a powerful workforce planning tool - check out our upcoming ebook. Be sure to save your copy now and sign up. You'll be among the first who will exclusively receive a PDF copy to download. "The Ultimate Guide to Call Center Agent Scheduling with Excel".

Did you find the article interesting and would like to share it with your colleagues? Download the article as a PDF.

Must-have forecasting E-Book

Download the free E-Book

The Must-Have Guide to Accurate Call Center Forecasting

What you will learn:

  • Foundations of Call Center Forecasting
  • Getting your data right
  • The importance of measuring variability
  • Critical factors you shouldn't miss
  • And much more!

Get E-Book

Most popular

Contact Center Forecasting Fundamentals #1: How to Forecast Workload
Contact Center Forecasting Fundamentals #1: How to Forecast Workload
Read more ...
How to Set up a Service Level Agreement (SLA) for Your Contact Center
How to Set up a Service Level Agreement (SLA) for Your Contact Center
Read more ...
How to Include Shrinkage in Your Planning Process
How to Include Shrinkage in Your Planning Process
Read more ...
10 expert tips for a successful Workforce Management job interview
10 expert tips for a successful Workforce Management job interview
Read more ...