I was a data analyst.

The differences between data analysts and data engineers are, data analysts work with customers in business side and require short-time solutions while data engineers run the processes under technical regulations and policies that need time for implementations.

As the working style of data analyst, one of their tools is…

Microsoft Excel

Please don’t be afraid. This program can increase your productivities and let you have more leisure times. Promise.

1. I need a text, not in other format

Sometimes we want a simple text but the excellent Excel transform what we typed to other format. We just add ' (single quote) at the first place and it will be treated as text.

I want this.

but Excel did this.

so add a prefix '

It works as expected.

2. Errors checking

There are 3 main functions to notice errors.

=ISERR(cell)
=ISERROR(cell)
=ISNA(cell)

=ISNA() identifies only value errors as “NA” stands for “Not Available”. This means the value is not available. =ISERROR() catches for all errors and =ISERR() catches all excepts “NA”.

Here is the example. column B is the result of formulas in A and D is the result of C.

• =8/0 gives #DIV/0! and this is operational error. Therefore, =ISNA() doesn’t work.
• =NA() gives #N/A and yes =ISNA() just works while =ISERR() doesn’t.
• =ASIN(2) gives #NUM! as the value must be -1 to 1. =ISNA() doesn’t work also.
• =TEX() gives #NAME? because this function name isn’t exist but there is =TEXT() and no, we can’t check this with =ISNA().
• =VALUE("A") gives #VALUE!. This function return an integer from numeric text and we try “A” so that’s an error. =ISNA() doesn’t work in this case.

3. Error replacements

We can identify errors so we can replace the errors.

=IFNA(cell, value)
=IFERROR(cell, value)

When we can catch errors with =ISERROR() and =ISNA(), we can run =IFERROR() and =IFNA() respectively to show the other responses.

There is no =IFERR().

4. Value comparisons

There are 2 formulas to compare values.

=cell1=cell2
=IFERROR(VALUE(cell1),cell1)=IFERROR(VALUE(cell2),cell2)

Here are the examples.

Ahh, notice that =A5=B5 gives FALSE but why? The value of B5 is '1 that is text not 1 which is number and that’s it. That is the table showing result from =TYPE().

Therefore, we combine =VALUE() to transform numeric text to number if any and =IFERROR() to catch the error if that value cannot be transformed to a number then an original value will be used, and compare with other values in the end.

5. Single click to make a chart

Go to tab: Home and click “Conditional Formatting”, there are many options.

• Data bar
  Add bar charts. Highest value will display the longest bar.
• Color scale
  Fill the cell background color. Depends on the color set we choose.
• Icon set
  Add icons to the cells
• Highlight cell rules
  Custom rules to apply colors to cells

6. Remove duplicated values

Here is the way we eliminate the duplicated data.

Go to tab: Data and click “Remove Duplicates”. There are 2 options.

6.1 Expanded Selection

Select this if we want to remove duplicates for multiple columns in a single row. For the example below, there is only 1 duplicate from row 1 and 4 that is (a, 1).

6.2 Original selection

Select this if we want to remove duplicates in only the selections.

7. Lookup

Here are 2 formulas the I used very often there.

=VLOOKUP(find, table, get, approx_match)
=INDEX(get, MATCH(find, table, match_type), column_no)

First, =VLOOKUP(). It is valid when the search column of “TABLE” is the first column. As the figure above, we need to find salary from their names. Therefore, we define “TABLE” to cover column of name through salary. And give “APPROX_MATCH” as FALSE to do exact match, otherwise it will run as approximate match.

Second, combination of =INDEX() and =MATCH(). This works when we cannot align the search column as the first column of “TABLE”. =MATCH() returns the index of “FIND” that it found in “TABLE” then =INDEX() will return the value of “GET” at the index.

Gives “MATCH_TYPE” as 0 to do exact match and “COLUMN_NO” also 0 as the first column of “GET” .

8. Count and Sum

The basic ones and we can use them in various situations.

• =SUM()
  Just a summation
• =SUMIF()
  Summation with a single condition. From the example, find sum of Salary (D2:D11) where Department (C2:C11) is “IT”
• =SUMIFS()
  Summation with multiple conditions. From the example, find sum of Salary (D2:D11) where Department (C2:C11) is “IT” and age (B2:B11) is greater than “30”
• =COUNT()
  Just count a number of numbers and booleans
• =COUNTA()
  Count a number of cells containing value.
• =COUNTBLANK()
  Count a number of cells containing nothing.
• =COUNTIF()
  Count a number of cells with a single condition. From the example, count a number of cells where Checked is “Yes”
• =COUNTIFS()
  Count a number of cells with multiple conditions. From the example, count a number of cells where Checked is “Yes” and Department is “Sales”

9. Dropdown

Let’s say we want to force input by a given value set. Here is the solution.

First, prepare the value sets then select cells that will be the input. Go to tab: Data and click “Data Validation”.

A dialog box will be appeared. Select “Allow” as “List” and the “Source” will be a range we can drag a mouse to select.

Now we’ve done. There is going to be a dropdown icon after the input cells. The error will be shown when the input value isn’t matched to the given list.

10. Freeze panes

Freezing panes is a good way to view large data with headers.

Go to tab: View and click “Freeze Panes”. There are many options and this time we choose “Freeze Panes”. We have to select a cell before. The figure below has B6 as the point.

As a result, we have frozen the pane above and left to B6. We can scroll to other points and the header rows and columns still be there.

11. Too many formulas, too long the files to open

Too often that I have to deal with so many functions and calculations, and found on the next time that the files are too long to open and to be prompted to work with. The reason is, the program has to process ALL calculations again once it is started.

I recommend to transform the stable result from formulas to solid values when we ensure the values have never updated. This operation is just copy then paste them as values.

12. Pivot table

Pivot table is one of the best features we can find in spreadsheet programs. We can design how the data will be represented in the beautiful views. Go to tab: Insert and select “PivotTable”.

Below is the sample Pivot table. Within ten clicks and we got the table to find salary of each members in a selected department. Let’s try yourself.

As I aforementioned, try this and you may improve your time with this.

Reference links: https://blog.hubspot.com/marketing/how-to-use-excel-tips