When working in the world of numbers with hundreds, thousands, or even a million lines of data, sometimes it can be hard to stay organized. Having disorganized data can make your information hard to locate, hard to share, and very hard to report or summarize. I am going to share with you a few key tips and tricks from a data analyst’s perspective that will help manage your data and make it easier to report on or make available to others in your network.

Find your data

Looking through hundreds to thousands of rows of data can be extremely daunting and intimidating. Sometimes if you are looking for specific data it’s important to keep the simple options in mind that can sometimes be overlooked. One of the most basic capabilities of any program or webpage is “find.” You can use it to search for a keyword or phrase. In the Microsoft programs, you can select choose “Find All” and get an entire listing of your search easily. To get to the “find” feature, you can press Ctrl+F in basically any application. In Microsoft programs “find” can be found in the home tab, and in webpages can be found in the options menu.

It could also be helpful to keep “replace” in mind, replace is used with “find” so you can find your word or phrase and replace it with a new word or phrase. Specifically in Excel, these features can be used to search one sheet or an entire workbook at one time. You also have the option to search for values and/or formulas to find what you are looking for. These tools can be extremely helpful when establishing Data Governance or just sifting through large sets of data.

Sheets and more sheets

As a Data Analyst, I often see and work with other users’ spreadsheets. One of the most common problems I see users making for themselves is not creating enough sheets in Excel to help with organizing data. I have commonly seen as many as six or more different sets of data in one Excel sheet organized vertically and/or horizontally parallel to other inconsistent data. This can be extremely hard to maintain and even harder to sort, reference, or chart.

An example of this would be multiple manually entered tables of data separated by a blank row or column. When your data is aligned this way it can make searching for or using functions with your data extremely difficult. It can also cause issues and confusion when sharing work with co-workers or team members. To avoid this, I strongly recommend putting different types of data on different sheets.

If you don’t feel confident in copying/pasting your information onto the new sheet you can always create a copy of your current sheet and delete different sets of data on each. As an example, if you have a department budget table, department fee table, and department sales table all in one sheet, you could copy that sheet twice and delete the repeated information. The new sheets would then be labeled “Budget,” “Fees,” and “Sales.”

To copy a sheet, you can right-click the sheet tab in the bottom left sheet navigation bar, and select “Move or Copy” from the menu. You can then move the sheet to a new book, reorder your sheets, and, by clicking the “Create a copy” checkbox, you can copy the sheet over to a new workbook book or into a new location in your current workbook. Moving your data to its own sheet will give it room to breathe and grow, you can find, reference, chart, and even print data more easily.

Format your data

Some may think that narrowing down data is the first step in managing data, but I would strongly disagree. My motto is “ You can never have enough data.” Though this means that sometimes having large sets of data can be unavoidable.

Having your raw data, especially if you can pull it directly from a source, gives you the freedom to filter out what you don’t need, but also allows you to retain it just in case. You can have the data available, but hide it until you need it. One of the easiest ways to accomplish this is to create a table from your data. Using tables makes referencing, filtering, and organizing columns a snap.

Tables are specifically designed to make managing and analyzing a group of related data easier. To create a table from your data, you can click any of your data cells and press Ctrl+t, make sure your data range is correct, and check the “My table has headers” checkbox if you already have headers.

You can also go to “Format as Table” in the home tab under styles, then choose a style for your new table. The table will automatically be created within the range you specified. Your header row will contain a box with dropdowns for filtering and sorting your data, from here you can filter out anything you don’t need. The table will also automatically calculate columns, if you enter a function into one cell of the row the table will fill it down through your entire data set.

You can customize how the table looks, filters, sorts, and reacts with your data. When clicked into any of your table cells the “Table Design” tab will become available in the top navigation bar. From here you can summarize data into a pivot table or even add a totals row.

The total row isn’t just “SUM” as the name would suggest, you can also average, count, or use most functions on the entire column of data just by pressing the drop-down “carrot” next to the number in the last row in your table. You can also resize your table, adding rows or columns by dragging the bottom right corner fill handle over the necessary columns or down the necessary rows.

If you start adding more data next to your table, the table will also include that data automatically. All these features are encompassed in the table and make short work of making your data easier to work with and look more appealing.

Name your data

Sometimes when you are working with multiple sheets and large sets of data, it can be hard to reference everything easily. You end up switching from sheet-to-sheet mid formula and it’s easy to get confused and lose your train of thought. One way to combat this is to name your data.

Giving sets of data a specific name will allow you to reference that data at any point. I find this feature extremely helpful when typing a function I can begin the data name and Excel will give me a list of all my available defined names as I go, so as long as I know what the name is all I have to do is click the name from the list or hit tab while creating a function and Excel does all the work to reference my data without me switching to another sheet to select it.

To name your data you will go to the “Formula” tab in the top navigation, then under Defined Names, click the “Name Manager” tag icon. From here you can edit the names of tables you’ve already recreated or edit and add new names. After naming your data you can click into the formula bar, start your formula, and use that new name as a reference instead of switching sheets and selecting the range. Although naming your data is not necessary, it can make entering functions a lot easier. You can reference your data anytime and, if you choose, from any sheet.

Validate your data

I have written articles about data and data governance before, and data validation is a big part of data governance. Although this article isn’t about data governance, it’s nice to utilize features with multiple benefits.

Data validation is used to limit or restrict the type of data that can be used in a certain cell. This can be especially useful when you are not the only user with access to a spreadsheet or workbook. Data validation will maintain that those cells are protected no matter what you want them used for, be it a number only, only text, a specific number of digits, a date, or a dropdown list.

To establish data validation, click the cell where you want the validation located, go to the Data tab in the top navigation bar, then under tools you will select “Data Validation.” The icon looks like a checked cell and a denied cell. You will then be prompted on what to allow in your selected cell(s). You can click the drop-down arrow and see a menu of your options including a custom criteria allowing you to input a custom function.

Data validations are sure to keep your data clean, making it easy for you and other users to sort and filter!

Protect your data

Although data validations protect certain sets of data from going outside of your specified limitations, even those cells can still be changed by other users. Excel is great at organizing and storing large sets of data, but sometimes when others have access functions and data can be accidentally deleted. Recovering data that may have been lost for an unknow time can be extremely difficult. One of the ways to prevent any loss of data or accidental rewriting is to lock or “Protect” your workbook or sheet.

To protect your sheet, you will need to know which cells you want protected. Do you want the document to be view only? Is there a certain set of cells the other users will need to change, but some need to be protected? Here’s how you can distinguish which cells are locked when you protect your sheet or workbook.

Select the cells you do not want locked, then right-click and choose “Format Cells” from the dropdown menu. In the Format Cells pop-up, you will click the “Protection” tab. For the cells you want to remain unlocked when you lock your sheet, you will uncheck the “Locked” checkbox. “Hidden” is for those cells that contain a formula you may want to hide.

Once you have unlocked the cells you want to stay available you will then go to the “Review” tab in the top navigation bar. Under Protect you can select “Protect Sheet” for only locking the current sheet you are in, or “Protect Workbook” to protect your entire document.

When you click the protect option you want to use, you will be prompted to choose what you want to allow other users access to changing in your locked cells. I would recommend allowing other users to format rows and columns, Sort, Use AutoFilter, Use PiviotTable and PiviotChart, and select the locked and unlocked cells which are automatically selected for you.

Keep in mind that passwords are optional. You can simply choose what to allow and hit okay to protect the sheet, hitting the “Unprotect” sheet will unlock all cells. Be sure that if at any point you use a password to protect your sheet or workbook you remember and/or write it down. There is not a way to recover passwords you set up.

Pro-tip, you can also choose from numerous Workbook protections in the Info option, under File. Sometime there are lots of hands in the pie, so to speak, and you just need other users to have specific access to review and maybe sort without worrying about deleting anything important.

Your data is KEY

The last tip I will share with you is just a personal preference of mine, creating a key. You know your data better than anyone else. If someone needed to assist you today could they find the data they are looking for? Do you have instructions on how to maintain your data’s integrity? Do you have validations you are using in multiple areas such as staff names, departments, fee types, etc?

A key to your data may be extremely helpful. What I use the key for primarily is data validations for all dropdown lists or any absolute references. I then add a text box and leave basic data management instructions for other users.

To include a textbox, go to the Insert tab in the top navigation bar, under Illustrations select shapes, textboxes are the option icon with an “A” in a square. Pro-tip: you can create a direct reference to a text box once it’s created by selecting the text box and typing “=” in the formula bar then selecting the desired reference cell. The key is there in case other users encounter an issue or restriction in the workbook, they can easily find out why and know how to handle any problems without flooding your inbox with questions.

Your data is only as good as you make it

Data is so important to almost every aspect of a credit union, and unorganized and unprotected data can impact employee’s work and negatively impact project progress. Using these techniques can be instrumental in keeping your data organized and safe until you need it. There are so many features and functions it’s hard to not be overwhelmed by it all, but it’s important to make your data and programs work for you, not against you. Make the most of your data and allow your data to work for you by using these tips and tricks!