Excel Series: Formulas and Functions Helen Anderson twitter logo github logo Sep 5 '18Updated on Jun 05, 2019・4 min read

Part of "Excel" series

Microsoft Excel, a fellow millennial and underrated analysis tool, turns 33 this month. It can’t replace tools for advanced statistical analysis, relational databases or sophisticated dashboards, but should be considered as part of your analysis toolkit.

This is part one of the tools I use for ad hoc analysis and data cleansing.

1 - Data cleansing for text

PROPER, LOWER, UPPER

To change the case of a cell using one functions insert a new column to the right of your data and use the functions below. Don’t forget to copy and ‘Paste Values’ to make the values remain.

``````=PROPER(A1)
=LOWER(A1)
=UPPER(A1)
``````

TRIM

Sometimes when importing data extra spaces can be added during the process before or after the data. To remove these, we use the TRIM function.

``````=TRIM(A1) /* removes any leading or trailing spaces from the text in A1 */
``````

CONCATENATE

This function is useful for putting a First Name and Last Name field back together with one step. This pastes B2 and A2 together in one cell with one space in between.

``````=CONCATENATE(B2," ",A2)
``````

RIGHT, LEFT, MID

These three functions are used to pull out certain parts of the cell without having to do this one by one. LEFT finds the cell entered and returns the number of characters to the left, in the example this would be four characters.

``````=LEFT(I2,4)
=RIGHT(I3,4)
=MID(I4,5,5)  /*MID finds the cell entered and returns
the number of characters requested from the start
point given, in this case, five characters from point five */
``````

Remember if you haven’t used the TRIM first, any trailing spaces will be included.

2 - VLOOKUP

VLOOKUP lets Excel do the hard work of matching up a value from one spreadsheet to another. It saves time and is an essential Excel tool.

Insert a column to the right of the column you are using as the reference. This is where your formula and results will go.
Type into your formula bar =VLOOKUP( and complete the formula using the instructions below

The formula means:

``````
=VLOOKUP

(

lookup_value -
You define a value for the formula to
look for by entering the cell here

table_array -
the range of columns to look in, highlight the
whole column where the reference is and drag
over to where the value you want to be returned is.

index_num -
number of columns from the look_up value
to the column where the result is.

range_lookup -
0 for an exact match,
1 for the closest match

)
`````` All done! Remember to copy and ‘paste values’ to ensure you the results remain and not the formula. If for any reason your lookup table moves or is deleted, so will your results.

BONUS TIP:
To hide the #N/A error that VLOOKUP throws when it can’t find a value, nest the IFERROR function to catch the error and return any value you like. In this case it will return ‘Not found’:

``````
``````

3 - Logical Functions for ad-hoc analysis

Here are six more useful functions to use in Excel. This time, logical functions that return a result based on the conditions

AND

Returns TRUE if ALL of the arguments evaluate to TRUE. In this example, the result is TRUE if a value in cell E2 is greater than 10, AND a value in F2 is less than 200

``````=AND(E2>10, F2<200)
``````

OR

Returns TRUE if ANY of the arguments evaluate to TRUE. In this example, the result is TRUE if a value in cell E2 is greater than 10, OR a value in F2 is less than 200

``````=OR(E2>10, F2<200)
``````

NOT

Returns the reversed logical value of its argument. ie. If the argument is FALSE, then TRUE is returned and vice versa. In this example, the result is TRUE if a value in E2 is NOT greater than 500.

``````=NOT(E2>500)
``````

IF

If you want to return a value based on whether a condition has been satisfied, but you don’t want to be limited to just TRUE or FALSE, then you can use the IF function.

IF statements are not only useful, they teach junior analysts the basics of Else/ElseIf statements.

The IF function needs 3 parameters:

The test
The result if the test is TRUE
The result if the test is FALSE

``````=IF(test,"true","false")
=IF(B2>C2,"Win","Lose") <- text needs to be in double quotes
``````

Nesting simply means to combine formulas, one inside the other, so that one formula handles the result of another.

``````=IF(B5>10,"Platinum",  -- if >10 then Platinum
IF(B5>3,"Gold",  -- if >3 then Gold
IF(B5>7.5,"Silver",  -- if >7.5 then Silver
IF(B5>2.5,"Bronze",  -- if >2.5 then Bronze
"Starter")))) -- else Starter
``````
``````SUMIF
=SUMIF(range,"criteria")
=SUMIF(B1:B4,">=40") - to add up only those values in a range
``````
``````COUNTIF
=COUNTIF(range,"criteria")
=COUNTIF(B1:B4,"South Africa") - to count up only those values in a range
``````

Big and small businesses use Excel because it’s easy to learn and allow analysts and stakeholders to speak the same language. If more sophisticated add-ons are required (Power BI, Power Pivot, Power Maps) these are open source and easy to use.

Excel offers functionality and plenty of bang for your buck with a usable interface and plenty of add-ons for scalability.

Part two:

This post first appeared on helenanderson.co.nz

Photo by Kaique Rocha from Pexels

Part of "Excel" series

DISCUSS (9) As someone in a more traditional analyst role, I love these series you have put together. I always opt for `SUMIFS` and `COUNTIFS` over `SUMIF` and `COUNTIF` as it makes it easier to add conditions later.

Thanks!

Excel is one of the most significant pieces of consumer/business software developed since it was created. It's birthday should be celebrated! Pretty much every business software needs to answer the question "why would people use this over excel?", and if there isn't a good answer it's likely not going to be successful. The fact that people can create games and ART in it always blows me away. It practically counts as its own platform...

Considering your data analyst background I will be very interested when you get to the part where you discuss lookups and joins. Personally I use excel infrequently but when I do I tend to use it like a pseudo DB, but awkwardly. It would be great to see some easy to follow examples from a professional 😀

I absolutely agree, it doesn't replace statistical software or a database but it does some seriously heavy lifting for most analytical tasks. Most companies have it so there's no need for special software and it's relatively easy to learn. Not every task needs a special tool.

In my next post I'll be getting into Pivot Tables and VLOOKUPS. In the meantime, Chandoo.org do the best job IMHO of explaining functions and formulas - chandoo.org/wp/advanced-excel-skills/

Sweet! Pivot tables, now that's my jam! 😀

If your data source is a delimited file, take a look at (Gnu) AWK (it is part of every single Linux distro). Except the relational part of your examples, AWK can handle everything here in a blink of an eye. Of course, if your final destination is Excel anyway, you can save the roundtrip and just use Excel.

When I use Excel I use a lot of expletives.
I'm not versed enough in R, and creating graphs using ggplot2 is kind of a pain. But it's much more performant and powerful. I have yet to try Julia.

But the simple fact that Excel refused to properly handle CSV files, or is even able to handle like 20MiB of data without crumbling is enough reason to look at different tooling.

Okay, so you got me very distracted ... dev.to/katiekodes/proper-casing-ex...

Classic DEV Post from Jan 25

What Bootcamp did you attend and would you recommend it?  