L1 Excel Test
1. What is the keyboard shortcut key to lock cell references in a formula
1 CTRL
2 ALT
3 F2
4 F4
Ans:
2. What are the shortcut keys for AutoSum?
1 ALT and S
2 CTRL and S
3 ALT and =
4 CTRL and =
Ans:
3. Company A is looking into four potential projects and will accept them if the IRR is 10% or above, as shown in cell E2.
1 IF(B2>=E2,"Accept","Reject")
2 IF(B2>=$E2,"Accept","Reject")
3 IF(B2>=E$2,"Accept","Reject")
4 IF(B2>=$E$2,"Accept","Reject")
Ans:
4. What are the keyboard shortcut keys to paste special?
1 ALT + H + V + F
2 ALT + H + V + P
3 ALT + H + V + O
4 ALT + H + V + S
Ans:
5. Calculate total Unit cost for the following region by using excel fromulas (use data in SaleOrders sheet)
East
Central
West
6. Get data for 27th April 2020
Units Unit Cost Total
Ans
7. With the help of conditional formatting colour code as follows
Condition
If value of unit cost = 0 than Green else Red.
Rep Item Unit Cost
Jones Pencil
Kivell Binder
Jardine Pencil
Gill Pen
Sorvino Pencil
Jones Binder
Andrews Pencil
Jardine Pencil
Thompson Pencil
Jones Binder
Morgan Pencil
Howard Binder
Parent Binder
Jones Pencil
Smith Desk
8. With the help of Index match function get data for the following.
Region Rep Item Units Unit Cost Total
6/25/2019
9. Grade the following student based their scores (use data in SaleOrders sheet)
Condition: A >90, B>75, C>= 60 and D below60
Student Score Grade
Jones 60
Kivell 82
Jardine 63
Gill 55
Sorvino 83
Andrews 92
Thompson 82
Morgan 63
10. Create a Pivot basisn SalesOrders Sheet and get value for the following and Add a % Coloumn in Pivot to see the % o
Note: Create pivot in new sheet and keep it for referance.
Item 2019 Total Cost 2020 Total Cost
Pencil
Binder
Pen
Desk
Pen Set
11. The shortcut key to “undo”
1 CTRL+PAGE UP
2 CTRL+HOME
3 CTRL+Z
4 CTRL+C
Ans:
12. =A7+A9 will subtract the two cells?
1 TRUE
2 FALSE
Ans:
13. Excel provides the following functions to be used except
1 AVERAGE
2 MIN
3 MAX
4 HIGH
Ans:
14. Export the Data to Power Query and remove the first coloumn & load the data below.
Ans:
15. Apply the Slicers on Existing Pivot table for Item and Year
Note: Pivot what you have created in Point 10, use the same Pivot to apply the slicer.
Item 2019 Total Cost 2020 Total Cost
Pencil
Binder
Pen
Desk
Pen Set
16. Get the Unit Cost , from Sales Orders Using Formula.
Rep Item Unit Cost
Jones Pencil
Kivell Binder
Jardine Pencil
Gill Pen
Sorvino Pencil
Jones Binder
Andrews Pencil
Jardine Pencil
Thompson Pencil
Jones Binder
Morgan Pencil
Howard Binder
Parent Binder
Jones Pencil
Smith Desk
16. Convert the below Date to 06-Jan-19 using Formula
Date Date Format
1/6/2019
1/23/2019
2/9/2019
2/26/2019
L1 Excel Test
or above, as shown in cell E2. What is the formula used in cell C2, which can be copied down to cell C3 through C5, to generate the
leOrders sheet)
oloumn in Pivot to see the % of 2019/2020
ough C5, to generate the results shown below
OrderDate Region Rep Item Units Unit Cost Total
1/6/2019 East Jones Pencil 95 1.99 189.05
1/23/2019 Central Kivell Binder 50 19.99 999.50
2/9/2019 Central Jardine Pencil 36 4.99 179.64
2/26/2019 Central Gill Pen 27 19.99 539.73
3/15/2019 West Sorvino Pencil 56 2.99 167.44
4/1/2019 East Jones Binder 60 4.99 299.40
4/18/2019 Central Andrews Pencil 75 1.99 149.25
5/5/2019 Central Jardine Pencil 90 4.99 449.10
5/22/2019 West Thompson Pencil 32 1.99 63.68
6/8/2019 East Jones Binder 60 8.99 539.40
6/25/2019 Central Morgan Pencil 90 4.99 449.10
7/12/2019 East Howard Binder 29 1.99 57.71
7/29/2019 East Parent Binder 81 19.99 1,619.19
8/15/2019 East Jones Pencil 35 4.99 174.65
9/1/2019 Central Smith Desk 2 125.00 250.00
9/18/2019 East Jones Pen Set 16 15.99 255.84
10/5/2019 Central Morgan Binder 28 8.99 251.72
10/22/2019 East Jones Pen 64 8.99 575.36
11/8/2019 East Parent Pen 15 19.99 299.85
11/25/2019 Central Kivell Pen Set 96 4.99 479.04
12/12/2019 Central Smith Pencil 67 1.29 86.43
12/29/2019 East Parent Pen Set 74 15.99 1,183.26
1/15/2020 Central Gill Binder 46 8.99 413.54
2/1/2020 Central Smith Binder 87 15.00 1,305.00
2/18/2020 East Jones Binder 4 4.99 19.96
3/7/2020 West Sorvino Binder 7 19.99 139.93
3/24/2020 Central Jardine Pen Set 50 4.99 249.50
4/10/2020 Central Andrews Pencil 66 1.99 131.34
4/27/2020 East Howard Pen 96 4.99 479.04
5/14/2020 Central Gill Pencil 53 1.29 68.37
5/31/2020 Central Gill Binder 80 8.99 719.20
6/17/2020 Central Kivell Desk 5 125.00 625.00
7/4/2020 East Jones Pen Set 62 4.99 309.38
7/21/2020 Central Morgan Pen Set 55 12.49 686.95
8/7/2020 Central Kivell Pen Set 42 23.95 1,005.90
8/24/2020 West Sorvino Desk 3 275.00 825.00
9/10/2020 Central Gill Pencil 7 1.29 9.03
9/27/2020 West Sorvino Pen 76 1.99 151.24
10/14/2020 West Thompson Binder 57 19.99 1,139.43
10/31/2020 Central Andrews Pencil 14 1.29 18.06
11/17/2020 Central Jardine Binder 11 4.99 54.89
12/4/2020 Central Jardine Binder 94 19.99 1,879.06
Developed by Contextures Inc. [Link] 04/30/2025
12/21/2020 Central Andrews Binder 28 4.99 139.72
Developed by Contextures Inc. [Link] 04/30/2025
