Post

When I worked with Microsoft Excel

This program can increase your productivities and let you have more leisure times.

tips & tricks
Posted
6 min read
<a href="https://unsplash.com/photos/white-printing-paper-with-numbers-Wpnoqo2plFA">Unsplash Mika Baumeister</a>
Unsplash / Mika Baumeister
When I worked with Microsoft Excel

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 I want this.
Excel did this but Excel did this.
add prefix so add a prefix '
as expected It works as expected.

2. Errors checking

There are 3 main functions to notice errors.

1
2
3

=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.

error checking

  • =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.

1
2

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

error replacements

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.

1
2

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

Here are the examples.

compare

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

menu conditional formatting

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

conditional formatting

  • 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.

remove duplicated

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).

Expanded selection Select "Expanded Selection"
Expanded selection done Expanded selection duplicates removed

6.2 Original selection

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

Original selection Select "Original Selection"
Original selection Original selection duplicates done

7. Lookup

Here are 2 formulas the I used very often there.

1
2

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

lookup

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.

vlookup flow

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” .

index match

8. Count and Sum

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

count sum

  • =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”.

tab data

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

data validation

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.

Select a value Select a value
Error occurred Error when input isn't valid

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.

menu Freeze

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.

pane freeze

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

Too often that I have to deal with too 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.

paste options Paste options
Paste values Paste 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”.

menu insert

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.

pivot

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

References

tips & tricks
Microsoft Excel spreadsheet
This post is licensed under CC BY 4.0 by the author.