Accounting Information Systems
Snug
ACC202_T2_2020_Excel_Workbook_W3.pdf
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
Contents Excel Fundamentals ................................................................................................................ 5
Week 3 ............................................................................................................................................. 5
❖ Exercise 1 – Getting started with Excel .....................................................................................6
❖ Exercise 2 – Alignment and Formatting ....................................................................................7
❖ Exercise 3 – SUM and Decimals ...............................................................................................8
❖ Exercise 4 – Percentage and AVERAGE Function .......................................................................9
❖ Practice Question 1 – SUM, Decimals and Percentage............................................................. 10
❖ Practice Question 2 – AVERAGE Function ............................................................................... 11
❖ Practice Question 3 – SUM and Sales Percentage ................................................................... 12
❖ Exercise 5 – Conditional Formatting Top 10 Values ................................................................. 13
❖ Practice Question 4 – Conditional Formatting and SUM Function ............................................ 14
❖ Exercise 6 – COUNT and COUNTA Functions ........................................................................... 15
❖ Exercise 7 – COUNTIFS and SUMIFS Functions ........................................................................ 16
❖ Practice Question 5 - COUNT and COUNTA Functions ............................................................. 17
❖ Practice Question 6 - COUNTIFS, SUM and SUMIFS Functions.................................................. 18
❖ Practice Question 7 – COUNTIFS Function .............................................................................. 19
❖ Practice Question 8 – COUNTIFS Function .............................................................................. 20
❖ Practice Question 9 – SUM and SUMIF Functions.................................................................... 21
Week 6 ..................................................................................................Error! Bookmark not defined.
❖ Exercise 8 – IF Function ............................................................... Error! Bookmark not defined.
❖ Exercise 9 – IF Function (True, False)............................................ Error! Bookmark not defined.
3
❖ Practice Question 10 – IF Function (True, False)............................ Error! Bookmark not defined.
❖ Exercise 10 – IFERROR Function ................................................... Error! Bookmark not defined.
❖ Practice Question 11 – IFERROR Function..................................... Error! Bookmark not defined.
❖ Exercise 11 – AVERAGEIF Function ............................................... Error! Bookmark not defined.
❖ Practice Question 12 – AVERAGEIF Function................................. Error! Bookmark not defined.
❖ Practice Question 13 – AVERAGEIF Function................................. Error! Bookmark not defined.
❖ Exercise 12 – Remove Duplicate .................................................. Error! Bookmark not defined.
❖ Exercise 13 – MAX and MIN Function........................................... Error! Bookmark not defined.
❖ Exercise 14 – Data Validation - Range .......................................... Error! Bookmark not defined.
❖ Exercise 15 – Data Validation – Drop Down Menu ........................ Error! Bookmark not defined.
❖ Practice Question 14 – Data Validation ........................................ Error! Bookmark not defined.
Excel Advance ................................................................................Error! Bookmark not defined.
Week 8 ..................................................................................................Error! Bookmark not defined.
❖ Exercise 16 – LOOKUP Function ................................................... Error! Bookmark not defined.
❖ Practice Question 15 – LOOKUP Function ..................................... Error! Bookmark not defined.
❖ Exercise 17 – VLOOKUP Function (Exact Match)............................ Error! Bookmark not defined.
❖ Practice Question 16 – VLOOKUP Function (Exact Match) ............. Error! Bookmark not defined.
❖ Exercise 18 – VLOOKUP Function (Approximate Match) ................ Error! Bookmark not defined.
❖ Exercise 19 – CLEAN Function ...................................................... Error! Bookmark not defined.
❖ Exercise 20 – TRIM Function ........................................................ Error! Bookmark not defined.
❖ Practice Question 17 – TRIM Function.......................................... Error! Bookmark not defined.
❖ Exercise 21 – RANK Function ....................................................... Error! Bookmark not defined.
❖ Practice Question 18 – RANK Function ......................................... Error! Bookmark not defined.
4
❖ Exercise 22 – PROPER Function.................................................... Error! Bookmark not defined.
❖ Exercise 23 – Freeze Panes .......................................................... Error! Bookmark not defined.
❖ Exercise 24 – CONCAT Function ................................................... Error! Bookmark not defined.
Week 9 ..................................................................................................Error! Bookmark not defined.
❖ Exercise 25 – Generate Data (Random numbers) .......................... Error! Bookmark not defined.
❖ Exercise 26 – Pivot Table ............................................................. Error! Bookmark not defined.
❖ Practice Question 19 – Pivot Table............................................... Error! Bookmark not defined.
❖ Practice Question 20 – Pivot Table............................................... Error! Bookmark not defined.
❖ Practice Question 21 – Pivot Table............................................... Error! Bookmark not defined.
❖ Practice Question 22 – Pivot Table............................................... Error! Bookmark not defined.
❖ Exercise 27 – Pie Chart ................................................................ Error! Bookmark not defined.
❖ Exercise 28 – Recommended Charts ............................................ Error! Bookmark not defined.
❖ Practice Question 23 – Recommended Chart................................ Error! Bookmark not defined.
❖ Exercise 29 – Relative and Absolute Reference ............................. Error! Bookmark not defined.
❖ Exercise 30 – Split Cells ............................................................... Error! Bookmark not defined.
❖ Exercise 31 – Collaboration ......................................................... Error! Bookmark not defined.
5
Excel Fundamentals
W eek 3
Overview
Exercises Practice Questions
❖ Exercise 1
Worksheet Basics
❖ Exercise 2
Alignment and Formatting
❖ Exercise 3
SUM and Decimals
❖ Exercise 4
Percentage and AVERAGE Function
❖ Exercise 5
Conditional Formatting Top 10 Values
❖ Exercise 6
COUNT and COUNTA Functions
❖ Exercise 7
COUNTIFS and SUMIFS Functions
❖ Practice Question 1
SUM, Decimals and Percentage
❖ Practice Question 2
AVERAGE Function
❖ Practice Question 3
SUM and Sales Percentage
❖ Practice Question 4
Conditional Formatting and SUM Function
❖ Practice Question 5
COUNT and COUNTA Functions
❖ Practice Question 6
COUNTIFS, SUM and SUMIFS Functions
❖ Practice Question 7
COUNTIFS Function
❖ Practice Question 8
COUNTIFS Function
❖ Practice Question 9
SUM and SUMIF Functions
6
Exercise 1 – Getting started with Excel
The objective of this exercise if to learn what are spreadsheet, worksheet, cell, rows, columns and formula
bar.
A spreadsheet is a special way of organizing data into rows and columns to make it simpler to read and
manipulate. The worksheet is comprised of columns (the vertical sets of boxes labelled A, B, C, etc), and
rows (the horizontal sets of boxes labelled 1, 2, 3, etc). At the intersection of each row and column is
a cell into which a user can enter either numbers or text. When you enter information or formulas into
Excel, they appear in the line of the formula bar.
7
Exercise 2 – Alignment and Formatting
The objective of this exercise if to learn font setting, alignment and styles. The Worksheet before font
formatting, alignment and style look as follows:
Once you have completed the exercise, your answer should look like this:
8
Exercise 3 – SUM and Decimals From the data given in the table below, let's learn to calculate the following:
a) total using the SUM formula
b) the grade in decimals
Once you have completed the exercise, your answer should look like this:
9
Exercise 4 – Percentage and AVERAGE Function From the data given in Exercise 3, let's learn to calculate the following:
a) the grade in % and
b) the average marks obtained in the quiz.
%. To calculate the average marks in the cell B10 =AVERAGE(B3:B9)
Once you have completed the exercise, your answer should look like this:
10
Practice Question 1 – SUM, Decimals and Percentage Using the data given below, practice and fill the yellow coloured-missing figures
a) total using the SUM formula,
b) the grade in decimals,
c) the grade in %
Once you have completed the practice question, your answer should look like this:
11
Practice Question 2 – AVERAGE Function Using the data given in the question, calculate the AVERAGE for each quiz and test.
Once you have completed the practice question, your answer should look like this:
12
Practice Question 3 – SUM and Sales Percentage Find the missing values (highlighted in yellow colour)
Once you have completed the practice question, your answer should look like this:
13
Exercise 5 – Conditional Formatting Top 10 Values
From the data given in the table below. Highlight the Top 10 items using the conditional formatting.
Once you have completed the exercise, your answer should look like this:
14
Practice Question 4 – Conditional Formatting and SUM Function
From the data given in the table below, practice:
a) applying SUM formula in the highlighted yellow cells
b) conditional formatting - data bars.
c) conditional formatting - colour scales
d) conditional formatting – Icon sets
Once you have completed the practice question, your answer should look like this:
15
Exercise 6 – COUNT and COUNTA Functions
Count function: From the data given in the table below, COUNT function counts the numbers in a range
of cells. It is important to note that it will count the numbers and will not cater to the text items.
=COUNT(C5:C13). COUNTA function: Count all the cells that are not empty in a range of cells.
=COUNTA(C5:C13)
Once you have completed the exercise, your answer should look like this:
16
Exercise 7 – COUNTIFS and SUMIFS Functions
COUNTIFS function: Counts just come of the items in a range of cells based on a condition of a set of criteria. For example, it can count how many times the word “Gigi” appears in a range of names . For
example, for Gigi, use the formula =COUNTIFS(B5:B13,F11)
SUMIFS function: Add just come of the numbers in a range based on a condition of a set of criteria. For
example, it can add sales for just the Sales Representative “Gigi” with the formula
=SUMIFS(C5:C13,B5:B13,F11).
Once you have completed the exercise, your answer should look like this:
17
Practice Question 5 - COUNT and COUNTA Functions
From the data given in the table below; find the missing values for COUNT and COUNTA.
Once you have completed the practice question, your answer should look like this:
18
Practice Question 6 - COUNTIFS, SUM and SUMIFS Functions
From the data given below, calculate COUNTIFS, SUM, SUMIFS
Once you have completed the practice question, your answer should look like this:
19
Practice Question 7 – COUNTIFS Function
From the data given in the table below; calculate the COUNTIF function for Buchanan and Dodsworth
Once you have completed the practice question, your answer should look like this:
20
Practice Question 8 – COUNTIFS Function
From the data given, practice the following requirement (part a, b, c, d and e):
a) count the number of cells in the range A2:A15 that equals 2
b) count the number of cells in the range A2:A15 that equals the value in cell A2
c) add the counts of cells in the range A2:A15 that equals to 4 to the count of the
cells in the same range equals to 8
d) count the number of cells in the range A2:A15 that contain a value greater than
the number 5
e) count the number of cells in the range A2:A15 that contain values not equal to
the value in A2
Once you have completed the practice question, your answer should look like this:
21
Practice Question 9 – SUM and SUMIF Functions
From the data given in the table below, calculate Q4 Sales using the SUM formula and SUMIF function
to calculate the meat sold in Quarter 4 of the year.
Once you have completed the practice question, your answer should look like this:
End of the workshop
