9 Excel Hacks for Spreadsheet-Based Workforce Planning

Lara Klinkenberg of injixo shares her advice for using Excel spreadsheets for 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.

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 Centre 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 1,000 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 many? There’s no exact numerical answer, but operate under the principle of 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 may 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 favourite 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!

This blog post has been re-published by kind permission of injixo – View the Original Article

For more information about injixo - visit the injixo Website

About injixo

injixo is a product of InVision, a market leader in WFM for over 25 years. InVision built on its knowledge and experience to launch injixo as one of the first cloud workforce management (WFM) solutions for contact centers on the market back in 2011. And gaining the accolade of first to market with AI-based forecasting. Since then, the injixo user community has exploded. And will continue to innovate and push the boundaries of WFM.

Find out more about injixo

Call Centre Helper is not responsible for the content of these guest blog posts. The opinions expressed in this article are those of the author, and do not necessarily reflect those of Call Centre Helper.

Author: injixo