Have you found yourself staring at a spreadsheet with rows and rows of data thinking, where do I even start? Trust me, I know working with your database can be overwhelming, but knowing some quick tips and tricks can make things go a lot smoother. Check out these 4 tips to relieve your stress and make managing your database a breeze.
To sort a column in your sheet, start by clicking into any cell that contains a header for your column. In the example below I have clicked into A1, or Name.
Next, click on Data in the menu, and then Filter in the toolbar. This adds filters to your column headers.
Click on the arrow on the column you want to sort, and select Sort A to Z.
The data in your column should now be in alphabetical or numerical order.
To highlight any duplicates that may be in your data, click on the letter of the column you want to review to select that entire column.
Next, click on Home in the menu and then Conditional Formatting in the toolbar. In the drop down menu, select Highlight Cells Rules and then Duplicate Values.
The following pop-up will appear; click OK.
Any duplicates in your selected column should now be highlighted in red.
To remove any duplicate rows that may be in your data, click on the letter of one of your columns to select that entire column.
Next, click on Data in the menu, and then Remove Duplicates in the toolbar.
The following pop-up warning will appear. Keep ‘Expand the selection’ checked and click ‘Remove Duplicates…’
Another pop-up will appear. Select ‘My data has headers’ in the upper right of the pop-up to change the column names from Column A to what your actual header is. Select which columns should be checked for duplicates and click OK.
For the example below, if you want to remove rows that have the exact same name, phone number and email address, you would leave all columns checked. If you want to remove rows that have the exact same phone number but you don’t care what the name or email is, you would just leave the Phone Number column checked and the only the first row with that number will remain; all other rows with that phone number will be removed.
A final pop-up should appear letting you know how many duplicate rows were found and removed, as well as how many rows remain.
To separate data within one column into multiple columns, use the Text to Column feature. In our example, we have our clients’ full names in one column, but want to separate these into two columns; one for first name and one for last name.
To start, click on the letter of the column you want to separate to select that entire column.
Next, click on Data in the menu, and then Text to Columns in the toolbar.
The following pop-up will appear asking you to pick your data type. Use delimited when the text you are wanting to split are varying lengths but are separated by the same character such as a comma, space, etc. Use Fixed width when the text you are wanting to split are the same length or aligned in columns. Select your data type and click Next. For this example, we’ll use delimited.
Next you’ll select the type of character(s) that is separating your text under Delimiters and then click Next. For this example, we’ll uncheck Tab and select Space, as there is currently a space between the first and last name in our column.
Finally, you’ll be asked to select your data format, as well as the destination for your data. It is very important to update your destination, otherwise it will overwrite any data in these fields. It is recommended to choose the first empty column at the end of your data. For our example, we already have data in columns A, B and C so we’ve updated the destination to $D$1, which means it will start putting the data in the first cell in Column D. Click Finish.
You should now have your column split based off of the parameters you entered. Add in additional column headers and you are ready to roll!
These 4 steps should make your database a little bit more manageable to work with. I hope you find them helpful and congrats on becoming a spreadsheet whiz!
Tired of feeling like just a number? Every Village Realtor is a partner in our shared mission to build a better real estate experience.