Mastering 33 Excel frequently-used functions with WPS College

Mastering 33 Excel frequently-used functions with WPS College

September 25, 2020

aaa
* Use shortcut key Alt+= to enter fucntion

2-4

4-5

3-2

  • At least one value present (Some functions like today function, doesn't need any value )
  • Use comma as separator in between each value
  • Number 1 can be a fixed value (For example, 2), a cell position (For example, B2), or a range of cells (For example, B2:B7). Use a comma as a separator between each range of cells. Excel will ignore any text cell during the calculation.

2-4

Do you know how to use Autofill in Excel?
Do you know how to open a functioning guild with one click?
Do you know how to quickly alter the value to avoid rote?
Keep on reading to mastering Excel❗️❗️

For this lesson, we are going to use two must-have functions to better get into details on how functions work in Excel. Here is the first one:

VLOOKUP

is an Excel function to look up data in a table organized vertically. Lookup a value in a table by matching on the first column.

It is one of the most commonly used function when you work with Excel. and it is super useful! Let get started 👉

15

Don't freak out by the look of this fucntion just yet, Let's look at a simple principle behind it.

5-3

2-4

6-2
Find correct couples from the long list
7-2

  1. Enter = and first few letters of VLOOKUP,
  2. Use 'Tap' on the keyboard to quickly select the options that pos up automatically

8-2

  1. Click on the target cell directly to fill up the first value "(Lookup_value)"
  2. Type comma as separator and then click on the second value "(table_array)"

9-3

  • This part indicates the desired value on column C to another table(The long list of Marvel couple) with the value we already have on column B. Therefore the retrieve part must start from column B and including the column C. For example, select area B:E
  1. Continue to add a comma and select your third value "(col_index_num)"

  2. Add one more comma and you can select the last value from options.

    *For this example, we are going to select the first option

10-2

Done!

16
2-4
12-1

  • Go to the menu bar and select 'Formulas'
  • Select the option 'Insert function'
  • Type in the keyword until you find the function you need, double click to insert

13

NETWORKDAYS

used to calculate working days exclude weekends and any dates identified in holidays. It is a great tool to calculate employee benefits that accrue based on the number of days worked during a specific term!

14

  • Enter the start data and the end date to the operation menu
  • Prepare your fixed holidays table, add the table to the third row 'Holidays'
Your are all set!

That's it for today!

Have you fully comprehend all those new bits of knowledge on these Excel tricks? Share with your friend and study together! You can also check out our Youtube channel for video tutorials.

2-5
11-1
Thank you for staying with WPS, hope to see you next time!

About Author

International operations department for WPS Office.