Data Consolidation

Educational data comes from a variety of resources- the student information system, the state reporting system, testing systems, teacher data, scholarship data, post-secondary data, and a variety of program data. Data consolidation is the corraling, combining, and storing of varied data in a single place. It lets users manipulate data from one point of access and begin to turn raw data points into insights that drive better decision-making. It helps to build the story of what the data is telling.

Best practices for consolidating data are:

• Check to be sure your data sources are compatible. If not, you will need to clean them up to be able to consolidate them.

• Maintain clean copies of your data. Always work off a copy of your data. Keep the original data intact.

• Be sure your character sets are standardized. The two most common character sets are ASCII and Unicode. This should not be an issue for you but want you to be aware of that terminology

  • ASCII has 7-bit codes that allow for 128 characters (upper case, lower case, numbers, symbols- etc.)

  • Unicode has 16-bit characters and allows for a much wider range of characters.

Challenges with consolidating data include:

• Hand-cleaning and coding can become time-consuming.

• More familiarity with spreadsheet formulas and functions is required.

• Data is usually spread across multiple locations.

• Security becomes an important issue.

It's easy to link data within a workbook.

The formula is: =Sheet1!A1 with Sheet being the sheet name.

Example: =Charts!A1 or ='Count if'!B1 with Charts and Count if being the sheet names

• Click on the cell where you want the data to be linked.

• Type =

• Click on the cell of the data you want to copy.

• Press return.

• Go to the cell where the data was copied and drag the formula down and across as needed.

Video

Linking data from one worksheet to another requires a formula but after you understand the Syntax, it's really not that difficult. Use the link below to access written directions and screenshots and a video tutorial on how to use this formula.

• Video Tutorial

While QUERY can be a bit complicated, there is an add-on for Google Sheets that makes querying different datasets much easier. Click the link below to access written directions, screenshots, and a video on how to use EZ QUERY to combine data.

Download the EZ QUERY add-on.

• Go to Sheets>Add-ons>Get Add-ons

• Search for EZ Query. It's the only one in the Google Workspace Marketplace

• Install the add on and allow permissions

• Download Crop Sheet Add-on and install it in the same manner

Video Tutorial