Category: Data Preparation

Microsoft Excel and Google Sheets are the first choices of many users when it comes to working with data. They’re readily available, easy to learn, and support universal file formats. When it comes to using a spreadsheet application like Excel or Google Sheets, the point is to present data in a neat, organized manner that is easy to comprehend. They’re also on nearly everyone’s desktop and were probably the first data-centric software tool any of us learned.

While spreadsheets are popular, they’re far from the perfect tool for working with data. There are some important risks to be aware of. We’re going to explore the top three things you need to be aware of when working with data in spreadsheets.

Risk #1: Beware of performance and data size limits in spreadsheet tools 

Most people don’t check the performance limits in spreadsheet tools before they start working with them. That’s because the majority won’t run up against them. However, if you start to experience slow performance, it might be a good idea to refer to the limits below to measure where you are and make sure you don’t start stepping beyond them.

Like I said above, spreadsheet tools are fine for most small data, which will suit the majority of users. But at some point, if you keep working with larger and larger data, you’re going to run into some ugly performance limits. When it happens, it happens without warning and you hit the wall hard.

Excel Limits

Excel is limited to 1,048,576 rows by 16,384 columns in a single worksheet.

• A 32-bit Excel environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process.
• 64-bit Excel is not subject to these limits and can consume as much memory as you can give it. A data model’s share of the address space might run up to 500 – 700 megabytes (MB) but could be less if other data models and add-ins are loaded.

Google Sheets Limits

• Google Spreadsheets are limited to 5,000,000 cells, with a maximum of 256 columns per sheet. (Which means the rows limit can be as low as 19,231 if your file has a lot of columns!)
• Uploaded files that are converted to the Google spreadsheets format can’t be larger than 20 MB and need to be under 400,000 cells and 256 columns per sheet.

In real-world experience, running on midrange hardware, Excel can begin to slow to an unusable state on data files as small as 50MB-100MB. Even if you have the patience to operate in this slow state, remember you are running at redline. Crashes and data loss are much more likely!

(If you’re among the millions of people who have experienced any of these, or believe you will be working with larger data, why not check out a tool like Inzata, designed to handle profiling and cleaning of larger datasets?)

Risk #2: There’s a real chance you could lose all your work just from one mistake

Spreadsheet tools lack any auditing, change control, and meta-data features that would be available in a more sophisticated data cleaning tool. These features are designed to act as backstops for any unintended user error. Caution must be exercised when using them as multiple hours of work can be erased in a microsecond.

Accidental sorting and paste errors can also tarnish your hard work. Sort errors are incredibly difficult to spot. If you forget to include a critical column in the sort, you’ve just corrupted your entire dataset. If you’re lucky enough to catch it, you can undo it, if not, that dataset is now ruined, along with all of the work you just did. If the data saves to disk while in this state, it can be very hard, if not impossible, to undo the damage.

Risk #3: Spreadsheets aren’t really saving you any time

Spreadsheets are fine if you just have to clean or prep data once, but that is rarely the case. Data is always refreshing, new data is continually coming online. Spreadsheets lack any kind of repeatable processes and or intelligent automation.

If you spend 8 hours cleaning a data file one month, you’ll have to repeat nearly all of those steps the next time a refreshed data file comes along. 

Spreadsheets can be pretty dumb sometimes. They lack the ability to learn. They rely 100% on human intelligence to tell them what to do, making them very labor-intensive.

More purpose-designed tools like Inzata Analytics allow you to record and script your cleaning activities via automation. AI and Machine Learning let these tools learn about your data over time. If your Data is also staged throughout the cleaning process, and rollbacks are instantaneous. You can set up data flows that automatically perform cleaning steps on new, incoming data. Ultimately, this lets you get out of the data cleaning business almost permanently.

To learn more about cleaning data, download our guide: The Ultimate Guide to Cleaning Data in Excel and Google Sheets

Cleaning data is essential to making sure that data science projects are executed with the highest level of accuracy possible. Manual cleaning calls for extensive work, though, and it also can induce human errors along the way. For this reason, automated solutions, often based on basic statistical models, are used to eliminate flawed entries. It’s a good idea, though, to develop some understanding of the top strategies for dealing with the job.

Pattern Matching

A lot of undesirable data can be cleaned up using common pattern-matching techniques. The standard tool for the job is usually a programming language that handles regular expressions well. Done right, a single line of code should serve the purpose well.

1) Cleaning out and fixing characters is almost always the first step in data cleaning. This usually entails removing unnecessary spaces, HTML entity characters and other elements that might interfere with machine or human reading. Many languages and spreadsheet applications have TRIM functions that can rapidly eliminate bad spaces, and regular expressions and built-in functions usually will do the rest.

2) Duplicate removal is a little trickier because it’s critical to make sure you’re only removing true duplicates. Using other good data management techniques will make duplicate removal simpler, such as indexing. Near-duplicates, though, can be tricky, especially if the original data entry was performed sloppily.

Efficiency Improvement

While we tend to think of data cleaning as mostly preparing information for use, it also is helpful in improving efficiency. Storage and processing efficiency are both ripe areas for improvement.

3) Converting fields makes a big difference sometimes to storage. If you’ve imported numerical fields, for example, and they all appear in text columns, you’ll likely benefit from turning those columns into integers, decimals or floats.

4) Reducing processing overhead is also a good choice. A project may only require a certain level of decimal precision, and rounding off numbers and storing them in smaller memory spaces can speed things up significantly. Just make sure you’re not kneecapping required decimal precision when you use this approach.

Statistical Solutions

Folks in the stats world have been trying to find ways to improve data quality for decades. Many of their techniques are ideal for data cleaning, too.

5) Outlier removal and the use of limits are common ways to analyze a dataset and determine what doesn’t belong. By analyzing a dataset for extreme and rare data points, you can quickly pick out what might be questionable data. Be careful, though, to recheck your data afterward to verify that low-quality data was removed rather than data about exceptional outcomes.

Limiting factors also make for excellent filters. If you know it’s impossible for an entry to register a zero, for example, installing a limit above that mark can eliminate times when a data source simply returned a blank.

6) Validation models are useful for verifying that your data hasn’t been messed up by all the manipulation. If you see validation numbers that scream that something has gone wrong, you can go back through your data cleaning process to identify what might have misfired.

Working with unstructured video data can be extremely difficult to tame! But don’t worry. With a few handy tips, the process becomes a lot more manageable.

Please note: this is our second article in a series on unstructured data. Click here to read the first installment, which explores indexing and metadata.

What Is the Problem With Unstructured Data?

Unstructured information is an unwieldy hodgepodge of graphic, audio, video, sensory, and text data. To squeeze value from the mess, you must inspect, scrub, and sort the file objects before feeding them to databases and warehouses. After all, raw data is of little use if it cannot be adequately leveraged and analyzed.

What Is Content Tagging?

In the realm of information management, content tagging refers to the taxonomic structure established by an organization or group to label and sort raw data. You can think of it as added metadata.

Content tagging is largely a manual process. In a typical environment, people examine the individual raw files and prep them for data entry. Common tasks include:

• Naming each item
• Adding meta descriptions of images and videos
• Splicing videos into frames
• Separating and marking different mediums

How to Use Content Tagging to Sort Unstructured Data

You can approach content tagging in several ways. Though much of the work is best done manually, there are also ways to automate some processes. For example, if an incoming file ends with a .mov or .mp4 suffix, you can write a script that automatically tags it as a video. The same can be done for graphics and text documents.

Tagging helps organize unstructured data as it provides readable context atop which queries can be crafted. It also allows for pattern establishment and recognition. In fact, photo recognition programs are, in large part, fueled by extensive tagging.

The Pros and Cons of Content Tagging

Tagging has its pros and cons. The downside is the manual labor involved. Depending on the amount of inbound data, it could take considerable resources to get the job done. Many businesses prefer to enlist third-party database management teams to mitigate costs and free up personnel.

As for pros, there are a couple. Firstly, content tagging makes data organization much more manageable. When you label, sorting becomes a snap. Secondly, tagging adds more value to data objects, which allows for better analysis.

Let’s Transform Your Unstructured Data

Leveraging AI-powered tools to perform complex data management tasks can save you money and increase efficiency in the long run. Inzata Analytics maintains a team of experts that focuses on digital data, analytics, and reporting. We help businesses, non-profits, and governments leverage information technology to increase efficiency and profits.

Get in touch. Let’s talk. We can walk you through the advantages of AI-powered data management and how it can boost your bottom line. See a demo of the platform here.

Back to blog homepage