[Guest post by Kristina Azarenko from Amasty]
Many of us use Excel every day as its layout makes it easy to structure information, analyze it, build different charts, etc. But hardly anybody knows how many features Excel really has (I don’t think Excel creators themselves know all of them). So we are using this program according to our needs and know a tiny piece of it. As an SEO, I use Excel day by day and can’t imagine my work without it. I can’t calculate how much time it saved me but I’m sure that would be an enormous number.
So here’s what I suggest: leave your “Excel comfort zone” and get to know how you can improve your productivity by using Excel formulas and functions.
Some theory first
If thinking about formulas and functions makes your face pale, don’t worry, I’m not into Math either. These Excel tricks are easy to understand and use for any non-tech person. But at first here is some theory and terminology.
Formula – this is something used for calculation of values in the cells. So you can easily find an average, sum, etc. of any given column or cells raw. All formulas begin with the equal sign.
Function – this is kind of a rule according to which you need to re-organize information in the given cells. Excel has an enormous amount of predefined functions and you can also create your own. One formula can contain multiple functions.
Parenthesis – they contain the information on the cells where formulas and functions should be applied. Cells indicators are called arguments.
And now after you have learned these words, let’s get down to useful formulas and functions.
If you need to calculate something, you can just put an equal sign in any Excel cell and type the numbers you need just like it’s shown in the first example above. However, if you need to get a sum of values from particular cells, you will need this:
You will need just a few clicks to get the needed information.
I’ll use the example from the previous point here. Imagine you need to find out the average amount of visits from different traffic sources, and then you will need to use:
This is a function you will be thankful for when you need to capitalize the first letter of each word in a cell. This is especially useful for writing meta titles or AdWords ads:
This function can save lots of time on formatting, check yourself!
I’ll continue talking about optimizing meta tags writing. A few years ago I used to check characters count in Word. It’s convenient but it requires additional actions that are actually not needed. Instead you can make use of Excel function that automatically shows characters count in a particular cell.
This function can also be used for AdWords ads and for any other thing you can apply it to.
From its name you can understand that this function joins information situated in multiple cells. For example, you have a list of URLs without the domain name and need to join it, so you just use:
You can join an unlimited number of cells here. Just remember that the arguments in parenthesis should be divided by semicolon.
This is probably one of the most useful Excel formulas you will learn. I bet you needed it a lot of times when you had a list of URLs and wanted to systematize them. And here it is:
Yes, this is a complex formula that is hard to understand from the first sight… and second, too. But let’s dig deeper.
LEFT is a function which shows that we need the left part of the URL.
A1 – you already know that this is an argument, i.e. the cell from which we get the information. Note that this is the only changeable part of this formula.
SEARCH helps to find the part of the URL we need.
9 – this is an ordinal number which shows where the domain name starts (after the protocol). There could have been an 8 but in this case sites with https:// won’t be included.
So we get something like that:
This formula is universal and will work in any case. As I’ve already pointed out above, only the cell argument is dynamic here, other parts are not changeable.
Bonus: Highlighting and Removing Duplicates
This is neither a formula nor function, just something very useful so I can’t miss it. Imagine you have thousands email addresses in your Excel workbook and need to know if there are duplicate values. Here is a quick way to do that without breaking a sweat.
So you choose the area you need and go to Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.
Duplicate values are now red. To delete them, go to Data tab in the header menu and choose Remove Duplicates. Now you have only unique values.
Now you have a set of useful Excel formulas and functions at your command. By using them in your daily work you will achieve higher productivity and better results. Moreover, you can complete boring tasks much faster.
Do you know other Excel tricks? Share them in comments.