The Dos and Don’ts of Restructuring Your Data in Excel

If you have experience working in Excel, you know that data occasionally needs to be restructured for various reasons. It may need a slight revamp to accommodate changes, such as the addition or removal of information. It can also become disorganized or otherwise difficult to navigate as it is scaled over time and edited by different users. Or, maybe, the original version of a data set is poorly arranged, which then requires a complete, top-to-bottom formatting overhaul. 

Depending on the size of your data set(s), restructuring missions can be long and intense processes. If you’re not very familiar with Excel best practices, jumping in headfirst may lead to formatting issues that are tedious to undo. Working in Excel isn’t difficult, but it can be tricky if you’re new to the tool or don’t have much experience working with other types of data programs. Before you get started, it’s advised that you brush up on a few of the “dos and don’ts” of restructuring data, specifically in Excel. 

That’s why I’ve put together a list of tips and tricks that can help you more quickly and efficiently restructure your data. I’ll also touch on the most common mistakes people make during reformatting and restructuring, so you can prevent them and save your future self from hours of reversing avoidable errors. 

Keep reading to find out more about my top “Dos” and “Don’ts” for restructuring data in Excel.

The “Dos” 


Do: Store your information in rows and separate it into different columns.

Excel screenshot - example of how to store information in rows and columns

Your data should be grouped and separated by type

As part of the restructuring process, you want to make sure that your data is separated into appropriately labeled columns and rows. Each category (name, region, item, etc.) should have its own column and/or row and individual entries should be in their own cells. 

In the above example, the second table’s first column is dedicated to the dates in the data set, and these dates are separated into individual rows and cells to indicate that they are separate entries. In the first table, you’ll notice that the data for dates, names, and units are scattered among the columns. Since the columns are not labeled and data is not clearly grouped together, although you can take a guess, there’s no real way to verify which pieces of data are connected and how.
 

Do: Use Excel tables and not auto-sort lists.

 

Excel screenshot - example of how to create an Excel table

Convert your data into a table using Ctrl+T or the Insert tab

If your data isn’t already in an Excel table, your next step will be converting it into one. To do this, just click Insert>Table and set the new table’s parameters. Make sure that the My table has headers box is clicked so your header text is read properly and not converted into the first row of data for your table. 

Auto-sort lists aren’t recommended during data restructuring. Any auto-sorting that you do will need to be undone to prevent function errors as you rearrange your table’s columns and rows. Since the auto-sort formulas are linked to specific columns, rows, and cells, any changes made to the table will negatively affect the accuracy of your table values. If you’d like your data auto-sorted anyway, you’ll want to wait until after it has been restructured and the format is finalized.  

Another bonus to converting your data from free text to tables is that column headings make it easier to read any included formulas. Since column headers are named, the guesswork of understanding the logic behind each formula is removed. The formulas (and the data overall) can instantly be read more intuitively.

 

Do: Intuitively name your Excel tables.

Excel screenshot - example of how to store information in rows and columns

Name each table after its contents for easier restructuring

Restructuring data can be complicated, and working with tables with misleading, hard-to-read, or confusing names adds an unnecessary level of complexity. 

If you have a table of data covering total sales that employees have made in a month, stay away from vague names like “Table 1”. When you’re struggling later to recall what data “Table 1” actually represents, you’ll regret not being more specific. 

Instead, avoid ambiguity and give each table a name that is indicative of its data contents. For example, opt for table names like tbl_Sales, tbl_Employees, tbl_Clients, Overtime_2021, etc.

You won’t be able to use spaces in your table names, so substitute dashes, commas, and underscores in their place. 

 

Do: Remember to plan for the future.

Excel screenshot - example of detailed tab information

Use a pivot chart or timeline slicer to visually represent your data

If your data set is separated into multiple time periods, best practice dictates that you don’t spread these various time periods across more than one tab. Instead, as you format your data, include a “Date” column of some sort that can help you differentiate between data sets where time is of importance. 

From there, there are a few ways that your data can be organized. You can insert a pivot chart as a visual representation of your information, with the date on the x-axis – ideal for quick and easy data assessment. You can also apply a “Timeline” slicer (filter) to the “Date” column and view specific time periods (day, month, year, and/or quarter) when you need a more in-depth view of the data. Combining these methods is also useful (and highly recommended) since one method can be used in lieu of the other, as necessary.

 

Do: Separate flat data entry fields from calculated fields.

Excel screenshot - example of how to properly categorize flat and calculated fields

As general practice, keep text-based data to the left and calculated data to the right

If you have data fields that contain calculations (read: formulas), these should be separated from the fields that contain only text. In the first table in the example, flat data and calculated data within the same columns, which is difficult to read and more challenging to navigate when adding or removing information from the data field. The second table’s data has been grouped and separated, with the calculations on the right and the flat text on the left. Since most languages are read from left to right, and text-based columns usually explain or refer to the calculations, arranging the data this way is a well-held best practice. 

 

Diving Into the Don’ts

DON’T: Arbitrarily separate your related data by tabs or individual sheets.

Excel screenshot - example of related data that is separated into two tabs

 Separating related information into multiple sheets makes reading data more challenging

Spreading data out over many tabs (or sheets) is a mistake people make quite often when trying to restructure their data. But, separating information this way can make it more challenging to interpret. Although separating data sets can simplify the organization of your data, not all data is easier to manage across several tabs. It also makes it more difficult to create accurate charts and pivot tables to illustrate the data. 

The best way to decide between one tab or multiple tabs is to ask yourself if separating the data will make it more challenging to understand or interpret the information in the data set. If the data is related, using multiple tabs can add a layer of inconvenience and create an unnecessary step in assessing the data set.  

A practical example? If you have a data set that includes expenses, sales, clients, and employees, you’ll want to find an intuitive way to group and separate this information. For the “expenses” and “sales” data, you can create a “Transactions” table to house these data sets and track both sets of information. The other two data groups, “clients” and “employees”, can now be placed in a separate table, which can then (if needed) be moved to its own tab. 

DON’T: Use blank rows and columns to separate related data

Excel screenshot - example of how to use and not use spaces to separate data

Blank rows and columns should never be used to separate related data sets

Related data should not be separated using either blank rows or blank columns. These blank spaces can falsely indicate to the viewer that the separated information is not related, interfering with how accurately the data is read and understood. Plus, if you need to convert data into a pivot table or a chart, the empty rows and/or columns won’t be accurately read by Excel. Err on the side of caution and keep your data free from unnecessary blank spaces that may affect formatting or comprehension.  

However, if the data is not related, you can separate it into different tables and even different tabs, if needed.   

 

DON’T: Use colors to identify or separate data.

Excel screenshot - example of how to use and not use color for data categories

Use labeled columns, and not colors, to distinguish between data categories

Instead of separating data by color (which may be tempting), use columns to differentiate between categories. Separating by color isn’t foolproof – even if you create a key, identifying categories this way isn’t practical. You’ll either spend chunks of time referencing the key to ensure that you’re handling the right data/data set, or you’ll have to memorize each category/color combination. If your data contains more than a few categories of information, memorization may be nearly impossible (or just not worth the effort).

Another reason to stay away from using color to identify data? It’s impossible to accurately report using either a Pivot Table or a Pivot Chart. If you want to add color to your spreadsheet, you can do so by navigating to Table Design>Table Styles and choosing from the range of options or by creating your own.

 

DON’T: Allow free-form text entry whenever possible.

Excel screenshot - example of how to use dropdown menus to avoid free-text entry

Your data should be grouped and separated by type

Whenever you can use dropdown lists instead of free-form text entry for your tables and spreadsheets, choose the dropdown lists! Of course, this won’t always be possible, but if you’d like to increase the speed and efficiency at which users can enter repeated text during data entry, dropdown lists are the way to go. You can also use these lists to limit the entries that can be made in a cell to force consistency across your data. 

To do this, select the cells you’d like to add a dropdown list to and navigate to Data>Data Validation and select List from the “Allow” dropdown menu. You’ll then need to type out each entry you’d like added to the list, separating each one by a comma. Major errors are much less likely to occur when using dropdown lists, since only the approved source text can be selected and added to the designated cells.
 

DON’T: Have multiple data types in a single column.

Excel screenshot - example of how not use data types in a column

Avoid having more than one data type (date, time, currency, etc) in one column

Excel screenshot - example of how to structure data types

Keep flat and calculated text separated into designated columns

In much the same way that flat and calculated data sets should be separated, you should make sure that multiple types of data are never placed in a single column. Consistency is key in Excel, and assigning specific columns for each data type is one way to prevent formatting issues and function errors. 

Doing this will also save time during data entry – simply select an entire column, choose its data type, and every entry you make in that column will be automatically converted. Manually assigning data types to various cells and rows in a column is counterproductive to Excel’s purpose – simplifying complex and mundane processes. 

Final Thoughts

Restructuring data won’t always be easy, but there are ways to make the process smoother and less issue-prone. You don’t have to be an expert in Excel to reformat and rearrange data, especially if you keep these key tips in mind. In no time, you’ll have created a more practical and intuitive data structure, designed for effortless navigation and scaling of your data for years to come.

Enhance Team Coordination with a Shared Calendar

Airtable’s built-in calendar goes beyond a simple scheduling tool. It transforms into a powerful hub for task management within your team. Project managers can create timelines, assign deadlines, and set reminders within the calendar view. Team members can access this centralized calendar to view upcoming tasks, track project progress, and collaborate seamlessly. This eliminates the need for multiple calendars and ensures everyone is on the same page, fostering efficient project execution.

Real-World Examples:

  • Marketing Team Campaign Management: Create a shared calendar to visualize brainstorming sessions, content creation deadlines, social media posting schedules, and campaign launch dates. This keeps everyone aligned and ensures a smooth marketing rollout.
  • Sales Team Activity Tracking: Use the calendar to track sales calls, client meetings, and follow-up deadlines. This allows managers to monitor individual and team performance, identify potential roadblocks, and provide timely support.

  • Product Development Team Sprint Planning: Block out dedicated times for user interviews, design sprints, development cycles, and testing phases on the shared calendar. This transparency ensures everyone involved knows what’s happening at each stage and fosters collaboration.

  • Customer Support Team Shift Scheduling: Create a visual schedule for customer support representatives, including breaks, training sessions, and planned maintenance windows. This ensures adequate coverage during peak hours and avoids customer service disruptions.

  • Content Creation Team Editorial Calendar: Manage a shared calendar for brainstorming sessions, content assignment deadlines, editing and revision cycles, and publication dates. This keeps the content creation process organized and on track.

Simplify Resource Management with Custom Spreadsheets

Airtable’s true strength lies in its ability to transform traditional spreadsheets into dynamic tools for resource management. Gone are the days of static data! Airtable spreadsheets can be customized with various field types, including formulas and automation. Teams can track project budgets, allocate resources efficiently, and monitor project timelines, all within a single, adaptable workspace. This level of customization empowers teams to manage resources effectively and identify potential roadblocks before they arise.

Real-World Examples:

  • Project Budget Tracking: Craft a custom spreadsheet to track project expenses, categorize costs, and compare them to allocated budgets. This allows for real-time financial health monitoring and facilitates informed resource allocation decisions.

  • Team Member Capacity Planning: Design a spreadsheet to visualize team member workloads, including deadlines, project assignments, and estimated time commitments. This helps identify potential bottlenecks, distribute tasks efficiently, and avoid overallocation of resources.

  • Inventory Management: Create a custom spreadsheet to track inventory levels, reorder points, and supplier information. This ensures you have the necessary resources in stock when needed and avoids stockouts or excessive storage costs.

  • Client Project Profitability Analysis: Develop a spreadsheet to track project costs, billable hours, and client fees. This facilitates profitability analysis for individual projects and helps identify areas for improvement in resource allocation for future client engagements.

  • Scenario Planning and Resource Forecasting: Build a custom spreadsheet to model different project scenarios and their resource requirements. This allows you to anticipate potential resource needs, plan for contingencies, and make data-driven decisions about resource allocation.

Boost Productivity through Automation

Airtable automates repetitive tasks, freeing up valuable time for your team to focus on high-impact activities. Imagine automatically sending task reminders, assigning new projects based on workload, or generating reports based on predefined criteria. Airtable’s robust automation features allow you to streamline workflows and eliminate manual data entry, boosting overall team productivity.

Real-World Examples:

  • Automated Task Management: Automatically create tasks in a to-do list when a new record is added to a specific Airtable base. This eliminates manual data entry and streamlines your workflow.

  • Email Reminders and Notifications: Automate sending email reminders or notifications based on deadlines or specific field changes in your Airtable base. This ensures you and your team stay on top of important tasks and upcoming events.

  • Data Pre-Population and Field Updates: Use automation to pre-populate fields with default values or automatically update related fields based on user selections. This reduces manual data entry errors and saves time.

  • Data Aggregation and Reporting: Automate the process of collecting and summarizing data from different Airtable bases into a single report. This saves time on manual data manipulation and provides a consolidated view of key metrics.

  • Content Approval Workflows: Create automated workflows that route content for review and approval based on pre-defined criteria. This streamlines the content creation process and ensures timely feedback and approvals.

Keep Projects Aligned with Real-Time Updates

Airtable Sync ensures your teams are constantly working with the latest information. This innovative feature lets you link projects across different bases, enabling real-time updates. No more refreshing pages or chasing down the newest document version – Airtable Sync keeps everyone in the loop, fostering smoother collaboration and faster project completion.

Real-World Examples:

  • Centralized Project Hub: Consolidate all project-related information in a single Airtable base, including tasks, deadlines, files, and discussions. With real-time updates, everyone can access the latest project information, fostering transparency and alignment.
  • Dynamic Dashboards and Reporting: Develop dashboards that automatically update with the latest Airtable data. This provides a real-time view of project progress, metrics, and potential roadblocks, allowing for data-driven decision-making and course correction.

  • Automated Dependency Management: Set up automations to track dependencies between tasks within your Airtable base. When a dependent task is completed, the automation can trigger updates or notifications for subsequent tasks, ensuring a smooth workflow and everyone stays on the same page.

  • Streamlined Communication and Feedback: Leverage Airtable’s commenting features to provide real-time feedback directly on tasks, files, or project updates within the platform. This eliminates the need for separate communication channels and ensures all project conversations are documented and centralized.

  • Collaborative Brainstorming and Planning: Use Airtable as a collaborative workspace where team members can brainstorm ideas, add notes, and update plans in real time. This fosters a dynamic and interactive environment where everyone can contribute and see the project evolve continuously.

Organize Workspaces by Linking Records

Airtable goes beyond simple data storage, allowing users to create relationships between records. Imagine linking customer information to related sales orders or connecting project tasks to their corresponding deadlines. This functionality fosters organization within your workspace, making tracking dependencies and visualizing project flow easier. Additionally, Airtable offers pre-built templates with pre-defined relationships, saving you time and ensuring a well-structured workspace from the outset.

Real-World Examples:

  • Content Management System: Create a system where content ideas in a brainstorming base are linked to corresponding production stages in a separate base. This allows you to track the progress of content pieces seamlessly across the entire creation process.

  • Sales Pipeline Management: Link opportunities in your sales pipeline to relevant customer information in a separate CRM base. This provides a holistic view of each prospect and streamlines the sales process.

  • Bug Tracking and Development Workflow: Link bug reports to corresponding tasks assigned to developers. This allows developers to easily access all relevant information for each bug fix and track the progress of their work.

  • Event Planning and Vendor Management: Link event details to specific vendors involved in the planning. This keeps track of all associated vendors, their services, and any communication threads related to each event.

  • Research Project Management: Link research topics to relevant source materials, such as articles or data sets. This allows researchers to easily access and reference the sources used for each aspect of their project.

Flexible Task Scheduling Options to Suit Any Project

Not all projects require the same management style. Airtable caters to this by offering a variety of task-scheduling options. Choose from list views for a simple overview, Kanban boards for visualizing workflow stages, Gantt charts for tracking project timelines, or gallery views for showcasing creative assets. This flexibility allows teams to select the view best suits their project management style and needs.

Real-World Example:

  • Agile Development Sprints: Airtable’s flexible scheduling allows you to define sprints (iterations) within your project base. Tasks can be assigned due dates within each sprint, enabling agile development teams to manage short work cycles and adapt to changing priorities.

  • Freelancer Project Management: Assign tasks with flexible deadlines based on freelancer availability and project milestones. This approach caters to the dynamic nature of freelance work, ensuring clear expectations while accommodating individual schedules.

  • Content Marketing Calendar: Schedule blog posts, social media content, and other marketing materials with specific publish times or deadlines. Airtable’s flexibility accommodates recurring tasks and seasonal content, ensuring a consistent and well-paced marketing strategy.

  • Event Planning with Staggered Deadlines: Manage complex event logistics with tasks due at various stages leading up to the event date. This could include tasks for venue booking months in advance and social media promotion closer to the event.

  • Long-Term Project Roadmaps: Airtable’s flexible scheduling isn’t limited to short-term tasks. Outline major project milestones and phases with tentative due dates, providing a high-level roadmap that can adapt as the project progresses.

Centralized Documentation for Easy Collaboration

Airtable isn’t just for data – it can also house all your project-related documents, images, videos, and files. This eliminates the need for scattered folders and email attachments, centralizing everything in one easily accessible location. Team members can collaborate on documents in real time, add comments, and ensure everyone has the latest version readily available. This fosters a culture of transparency and streamlines communication within your team.

Real-World Examples:

  • Single Source of Truth: Consolidate all project-related documents, such as meeting notes, design specifications, and user manuals, within a dedicated Airtable base. This ensures everyone can access the latest and most accurate information, eliminating confusion and wasted time searching for scattered documents.

  • Version Control and Revision History: Airtable tracks changes to documents stored within bases. This allows teams to collaborate on documents in real-time, see revision history, and revert to previous versions if necessary. This fosters transparency and prevents accidental overwrites.

  • Inline Collaboration and Feedback: Airtable facilitates document collaboration directly within the platform. Team members can add comments, suggestions, and questions on specific sections of documents, streamlining feedback exchange and eliminating the need for separate communication channels.

  • Conditional Content Visibility: Leverage Airtable’s view functionality to control document visibility based on user roles or project phases. This ensures team members only see the information relevant to their tasks, reducing information overload and maintaining a focused workspace.

  • Centralized Knowledge Base Creation: Develop a comprehensive knowledge base within Airtable to store best practices, FAQs, and troubleshooting guides. This centralized repository empowers team members to find answers independently, reducing the need for repetitive questions and promoting knowledge sharing across the organization.

Expand Functionality with Custom Apps

Airtable’s open API unlocks a whole new level of customization. Developers can leverage this API to create custom applications that fit your business needs perfectly. Imagine integrating your CRM system with Airtable for seamless customer data flow or connecting your project management tool for a unified workflow. The possibilities are endless, allowing you to tailor Airtable to your unique business processes and maximize its functionality for your team.

Real-World Examples:

  • Advanced Data Integration: Airtable offers robust built-in integrations, but the Open API allows for even deeper connections. Imagine a custom app that pulls real-time weather data from an external API and integrates it with your project timelines in Airtable. This could be crucial for construction projects where weather can significantly impact scheduling.

  • Customizable Workflows and Automations: While Airtable automations are powerful, they might not handle every complex scenario. The API allows you to build custom apps that trigger multi-step workflows based on specific events in your Airtable base. This could involve sending automated notifications to multiple channels or initiating external actions upon data changes.

  • Machine Learning and AI Integration: The API allows you to incorporate machine learning or AI functionalities into your Airtable workflows. Imagine a custom app that analyzes customer support data in your Airtable base and suggests potential solutions or categorizes incoming tickets based on sentiment analysis.

  • Custom User Interfaces and Branding: Airtable offers a functional interface, but the API allows you to develop custom apps with a user interface tailored to your specific needs and branding. This could be crucial for creating public-facing portals or client dashboards that seamlessly integrate with your Airtable data.

  • Advanced Data Analytics and Reporting: Airtable’s built-in reporting features are useful, but for in-depth data analysis, you might require more. The API allows you to connect your Airtable data with powerful analytics tools. This could involve building custom dashboards with interactive visualizations or generating comprehensive reports that go beyond Airtable’s native capabilities.

 

Conclusion

Airtable offers a powerful and versatile toolkit that can revolutionize your company’s operations. By implementing the strategies outlined above, you can streamline workflows, optimize resource allocation, and foster seamless team collaboration. Imagine a work environment where everyone has access to the latest information, tasks are completed efficiently, and projects stay on track. Airtable can be the key to unlocking this efficiency level and propelling your business towards sustainable growth.

But what if Airtable’s pre-built features aren’t quite what your team needs?  ProsperSpark’s team of Airtable consultants and developers can help.  We craft custom Airtable solutions tailored to any team’s needs and workflows.  Whether you need help with automation, data management, or building a complex project management system, ProsperSpark can turn your Airtable vision into reality.

Here are a few more resources to learn more about Airtable and its potential for your business:

ProsperSpark’s Airtable Consulting Services

Airtable for Beginners Guide

Airtable Community Forum

Get On-Demand Support!

Solve your problem today with an Excel or VBA expert!

Follow Us

Related Posts

The Power of Using Airtable

The Power of Using Airtable

We have been keeping records and data since the beginning of time. From the earliest cave drawings to modern-day spreadsheets, we have always had a need to organize our records in an efficient and easy-to-understand way.  But let's face it. In today's rapidly evolving...

Exploring Fractional Business Services

Exploring Fractional Business Services

With the current economy, startup owners are looking for ways to decrease expenses and make the most out of their resources, and fractional business services are a great way to do just that. It seems everyone is hopping on the consultancy bandwagon, with contract...

The Power Of An Infographic

The Power Of An Infographic

Every day, we are bombarded with incredible amounts of information. From the books we read to our online and in-person classes, and even in the workplaces we attend, the data processed by our brains is staggering.  In order to make sense of all this information, our...

Microsoft Statistics – 20 Stats You Should Know

Microsoft Statistics – 20 Stats You Should Know

Image by Wachiwit on Shutterstock Microsoft has grown from its focus on operating systems to include productivity, SaaS, and various other IT products. Most people in the developed world have used Microsoft Excel, Word, and other Office apps—making Microsoft 365 one...

Why The External Consultant Model Works

Why The External Consultant Model Works

In 2020, data revealed that consulting was a $250 billion dollar industry. Figures were down at the time due to the pandemic, so it's safe to assume that this number has only grown in the past year. There's no doubt that consultancy holds appeal for many businesses;...

Pin It on Pinterest

Share This