Accounting Information Systems

1

Excel Workshop

Subject Code: ACC 202

Subject Name: Accounting Information Systems

Workshop Dates: Week 3, Week 6, Week 8 and Week 9

Workshop Location: Computer Lab

Objective: Microsoft Excel is the most used spreadsheet program in many business

activities; therefore, these workshops are aimed to provide you with the

fundamental and some advanced excel features.

Workshop Outline: The workshop questions are divided into Exercises and Practice

Questions. The lecturers are required to explain the concept with the help

of given exercises. The students are required to practice similar kind of

questions through practice questions.

2

Excel Advance

Week 8

Overview

Exercises Practice Questions

❖ Exercise 16

LOOKUP Function

❖ Exercise 17

VLOOKUP Function (Exact Match)

❖ Exercise 18

VLOOKUP Function (Approximate Match)

❖ Exercise 19

CLEAN Function

❖ Exercise 20

TRIM Function

❖ Exercise 21

RANK Function

❖ Exercise 22

PROPER Function

❖ Exercise 23

FREEZE Panes

❖ Exercise 24

CONCAT Function

❖ Practice Question 15

LOOKUP Function

❖ Practice Question 16

VLOOKUP Function (Exact Match)

❖ Practice Question 17

TRIM Function

❖ Practice Question 18

RANK Function

3

Exercise 16 – LOOKUP Function

From the given data, let's find out which type of product was ordered in Order number 10251.

Once you have completed the exercise, your answer should look like this:

4

Practice Question 15 – LOOKUP Function

From the table given in the question, lookup which fruit was order in Order Number 10249 and 10252:

Once you have completed the practice question, your answer should look like this:

5

Exercise 17 – VLOOKUP Function (Exact Match)

VLOOKUP retrieves data based on column number. When you use VLOOKUP, imagine that every column

in the table is numbered, starting from the left.

Once you have completed the exercise, your answer should look like this:

6

Practice Question 16 – VLOOKUP Function (Exact Match)

From the given data, find the following information for a Student ID: 622

Once you have completed the practice question, your answer should look like this:

7

Exercise 18 – VLOOKUP Function (Approximate Match)

You'll want to use approximate mode in cases when you're looking for the best match, not an exact match.

From the data given, find the commission rate based on a monthly sales number.

Once you have completed the exercise, your answer should look like this:

8

Exercise 19 – CLEAN Function

The CLEAN function is categorized under Excel Text functions. The function removes non-printable

characters from the given text.

Once you have completed the exercise, your answer should look like this:

9

Exercise 20 – TRIM Function

The Excel TRIM function strips extra spaces from the text, leaving only a single space between words and

no space characters at the start or end of the text.

Once you have completed the exercise, your answer should look like this:

10

Practice Question 17 – TRIM Function

Practice using the Trim formula to eliminate the unnecessary spaces in the text.

Once you have completed the practice question, your answer should look like this:

11

Exercise 21 – RANK Function

RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest

time) values, using an optional order argument.

Once you have completed the exercise, your answer should look like this:

12

Practice Question 18 – RANK Function

From the data given in the table below, find out the rank of all the students

Once you have completed the practice question, your answer should look like this:

13

Exercise 22 – PROPER Function

From the data given in the table below, apply the PROPER Function

Once you have completed the exercise, your answer should look like this:

14

Exercise 23 – Freeze Panes

From the data given in the table below, freeze a row and freeze a column. To freeze the column/row in

a sheet, click View tab > Freeze Panes.

Once you have completed the exercise, your answer should look like this:

15

Exercise 24 – CONCAT Function

From the data given in the table below, apply the CONCAT Function

Once you have completed the exercise, your answer should look like this:

End of the workshop