May 26, 20204 min read

Improve Team Consistency With Tables and Dropdowns in Excel

One of the most commonly used "modern" features in an office today is the ability to co-author or co-edit a document with multiple people at the same time.

Being able to work in an excel sheet at the same time as a co-worker is simply fantastic. You can view what you need to view and quickly edit what you need to edit. However, this can present new challenges with staying consistent as a co-worker might use a worksheet in very different ways.

Two Excel features that improve co-authoring consistency+ are Tables and Dropdowns:

When you create a table in Excel, you enable features that allow you to to organize, group, and utilize your data more easily.
When you implement Dropdowns into a table, it becomes easier for multiple people to become more consistent with their data entry

Here's an example scenario

The setup

Carol is the manager of a small team at a local firm that is responsible for investigating accounts receivable issues for clients that are very behind on invoices. They have a great accounting system that lets them keep track of everything, but there are several special circumstances where his team needs to investigate the situation.

Their current process involves Carol emailing a member on her team to look into a particular situation. She meets with her team every week to discuss the status of the various situations. Most of the time following up and talking to their client leads to a happy resolution, but sometimes they need to escalate to their legal team. Once it is escalated, their team no longer needs to track it.

As the firm has grown, so has their workload. It has become more difficult to stay on top of their various tasks. Their last team meeting to discuss open issues was not productive. It's difficult for Carol to know who is doing what and where she can help. Some team members are very organized but others are struggling. Carol knows there is a better way, but isn't sure what to do.

Let's use Tables and Dropdowns to modernize their workflow

The first challenge is that Carol and her team do not have a unified view of their tasks. Technology can help solve this problem, but choosing the right technology is its own challenge as there are many options available.

In this case, Carol's accounting software does not have a way to track tasks, and the follow up process is something she wants to have very specific control over. She determines that Microsoft Excel is the best fit for her, especially since her firm is already using Office 365.

Let's bring all the data into one place

Everyone on the team is using their own methods to do their work. This is causing a problem since the data is everywhere. Carol meets with her team to sort out a way they can put all of their data into a single table with a goal to make things as simple as possible.

She creates a table in their new shared worksheet with the table headers they agreed to. Everyone enters their data and is instructed to use this single worksheet going forward.

Now that everything is in one table. There is a quick an easy way for everyone to be on the same page. The ability to filter and sort data is automatically enabled and will update as more data is added to the table:

Anyone on the team can add to the list by typing into the next row in the table:

After an adjustment period, everyone is much happier! Team meetings are much more productive and Carol feels much more confident in the direction their team is headed. On top of that, Carol now has a way to create charts and provide visual status reports to the management team.

Carol realizes that now that everyone is used to using the sheet, it is highlighting a problem with using the right statuses and labels in their new sheet. She talks to the team, and it gets better, but they are still running into problems with typos and people forgetting the right label to use.

Let's make things more consistent by using dropdowns

The drop-down list feature in Excel exists to solve consistency problems like this. Carol and her team decide that they want to implement dropdowns for 3 columns in their table.

To keep things organized they create 3 new tables, one for each column that will have dropdowns.

After updating their table to match the new labels that they agreed on. They then implement the data validation feature used to create drop downs for each column in the table:

Now, whenever a new entry is added to their table, dropdowns exist and an error pops up whenever incorrect labels are used:

Now the worksheet used by their team is even easier to work with! Consistency issues with statuses and reasons are no longer an issue. Additionally, since this is an Excel sheet in Office 365, the document and be opened from multiple devices and the data table can be used by other applications!

Wrapping up

Excel is not going to be the best fit for every situation. However, it's an extremely versatile tool that can help out in many situations. It's important to consider your immediate and long term needs when you decide on your approach to modernize workflow.

In addition to features in your existing business management software, if you are using Microsoft Office 365, you are going to have many different tools available to consider. That said, using an Excel Table with Dropdowns might just be the right fit!