Productivity

Time-Saving Tips to Make You an Excel Expert

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.

Tip #1: Sorting Columns

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.

excel-sort-columns

Next, click on Data in the menu, and then Filter in the toolbar. This adds filters to your column headers.

How to add filters to columns in excel


Click on the arrow on the column you want to sort, and select Sort A to Z.

How to sort a column alphabetically in excel using a filter



The data in your column should now be in alphabetical or numerical order.

Example showing a column sorted alphabetically in excel



Tip #2: Highlighting Duplicates

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.


How to highlight a column in excel


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.


How to select duplicate values in excel



The following pop-up will appear; click OK.


Confirmation popup for duplicate values in excel


Any duplicates in your selected column should now be highlighted in red.


Result showing duplicate values highlighted in excel


Tip #3: Removing Duplicates

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.


How to select a column in excel


Next, click on Data in the menu, and then Remove Duplicates in the toolbar.


How to remove duplicates in excel



The following pop-up warning will appear. Keep ‘Expand the selection’ checked and click ‘Remove Duplicates…’

Remove duplicates warning in excel

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.


Remove duplicates in excel


A final pop-up should appear letting you know how many duplicate rows were found and removed, as well as how many rows remain.

Confirmation popup for removing duplicates in excel


Tip #4: Text to Columns

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.


How to select a column in excel


Next, click on Data in the menu, and then Text to Columns in the toolbar.


How to select Text to Columns in excel


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.


Step 1 of 3 in Text to Columns Wizard in excel


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.

Screen Shot 2018-07-25 at 10.29.58 AM



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.

Step 3 of 3 in Text to Columns wizard in excel



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!

Showing end result of text to columns in excel



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.

New call-to-action

News From Our Blog

Related Posts

Productivity

The Ambiance of an Open House

Creating the perfect ambiance can make or break an open house. Here are some easy details that can take your open house to the next level.

Marketing

Email Subject Lines to Increase Open Rates

According to HubSpot, 35% of email recipients open email based on the subject line alone. Here are some subject line ideas to increase your open rates.

Village Life

Terrible Real Estate Pictures

'Terrible Real Estate Pictures' is a collection of the worst of the worst from around the country when it comes to showcasing properties.

View all the latest blog posts