My 15 Secrets* to Convincing People I'm Good at Excel

15 Excel Formulas and Features that will convince everyone you're an Excel Master

(Even if you don't know the difference between Concatenate and Text Join)

So, these aren't secrets exactly, nor is this a tutorial on how everything works. This is a collection of Excel features and formulas that I rely on and discovered casual Excel Users might not know about.

I'm no expert. I'm not the best at logic arguments and am not ashamed to say that Google has been my strongest tool for understanding Excel. But I am sure there are a few people out there who already know some basic formulas and could use a couple of suggestions to bump up their skills to the next level. So, if you find yourself manually fixing the information in Excel documents or doing the same steps over and over, this article should provide some ideas on ways to make your life (slightly) easier by mastering these 8 formulas.

The great thing about Excel is that you only need to know a small handful of formulas to do most of your tasks. Better yet, you can combine these formulas with the features I mention to supercharge your experience and cut down your Excel-related stress.

?? 7 Excel Features Everyone Should Know

1. Filter - Allows you to filter and sort columns for sheet organization, data cleaning, and analysis

2. Text to Columns - Lets you split up the contents of a cell into adjoining columns for sheet organization, data cleaning, and analysis

3. Remove Duplicates - Does exactly what it sounds like, but the cool thing is you get to decide which columns should be assessed when identifying duplicates. It's fantastic for data cleaning.

4. Record Macro - To make repetitive tasks go by faster - especially tasks related to sheet formatting. It can be tricky, so I recommend watching a few demonstrations before trying it on your own.

5. Set Data Types - Excel can identify Countries and States based on Zip Codes or abbreviations and can and convert prices into different currencies.

6. Copy/Paste as Value - Allows you to copy and paste cells/rows/columns in a spreadsheet without formulas or cell formatting.

7. Conditional Formatting - Set up formatting rules based on cell content.

Need to identify dates where total sales exceeded a certain amount? Use the Greater Than..." option.

Want to identify the webpage that received the top 10% of traffic last month?

Excel has you covered with the "Top 10%" conditional formatting rule all set up!

I especially love combining the Highlight Duplicate Values rule with the "Sort by Color" then "Filter By Color" to identify datasets that share identical attributes. See what I mean about combining features?

??My 8 Favorite Formulas for Excel:

1?? Text Join (=TEXTJOIN) Combines the text in two or more cells together with a designated separation like a space or period.

I've used this to create spreadsheets of social media posts for bulk uploading into #hubspot and #hootsuite

?

2?? Concatenate (=CONCAT) Similar to Text Join, Concatenate allows you to add text to one or more cells together.

I've used this to add a website's main URL to another cell which has the slug.

?

3?? VLookup (=VLOOKUP) This one is a bit complicated, and I would recommend watching a few examples first. It allows you to search for the contents of one cell (Like the abbreviation of a state) within a column in another Excel spreadsheet or document. If the value is found, the contents of the cell in one of the columns to the right of the matching value are returned.

For example, if I have a long column of state abbreviations, and I don't want to copy/paste over and over again, I'd add a blank column next to the abbreviations and use a VLOOKUP formula to search for each abbreviation in another document and return the full name of the state. So "NJ" returns a value of "New Jersey".

?

4?? Proper (=PROPER) Capitalizes the first letter of a word after each space.

Incredibly handy for cleaning First Names before importing them into your CRM. Who wants to get an email that says, "Hello MOLLY,"?

?

5?? Length (=LEN) Calculates the total number of characters in a particular cell.

I've used this to measure the length of social media posts for bulk scheduling and make sure they don't exceed the character limit.

?

6?? Identify if the cell has a number in it =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},CELL))>0

Lets you find those sneaky numbers in cells that should be all text

?

7?? Left (=LEFT) Allows you to manipulate the contents of a cell based on its relation to the left-most position in the cell for example:

Remove EVERYTHING but the first character from the left of a string =LEFT(CELL,1)

or

Remove the LAST character from the right of a string.

=LEFT(CELL, LEN(CELL) - 1)

Works great if you have a very large data set and want to organize your work by the first letter of someone's last name. Adjust the number to change the number of characters that need to be removed.

?

8?? Left (=RIGHT) Allows you to manipulate the contents of a cell based on its relation to the right-most or left-most position in the cell for example:

Remove EVERYTHING but the last character from the right of a string

=RIGHT(CELL,1)

or

Remove the LAST character from the left of a string

=RIGHT(CELL, LEN(CELL) - 1)

This is particularly helpful in removing strings of random numbers or symbols from a group of cells. Like if you are given a full list of membership numbers, but for privacy reasons, want to remove all but need the last 4 digits.

?

I'm constantly on the lookout for new formulas to add to my arsenal, so let me know your favorites in the comments below.

Photo by Rubaitul Azad on Unsplash