Developing and Using Complex Spreadsheets: A Practical Guide

Desklib provides solved assignments and past papers to help students succeed.

DEVELOP AND USE COMPLEX
SPREADSHEETS
1

Table of Contents
Assessment event 1.........................................................................................................................3
Question 1.......................................................................................................................................3
Question 2.......................................................................................................................................4
PR 1, 2, 3 & 4..................................................................................................................................4
PR 5.................................................................................................................................................9
Question 3.....................................................................................................................................14
PR 1, 2...........................................................................................................................................14
PR 3, 4...........................................................................................................................................14
PR 5, 6, 7, 8...................................................................................................................................14
Assessment event 2.......................................................................................................................15
(Task 1.1).....................................................................................................................................15
PR 2...............................................................................................................................................15
(Task 1.2)......................................................................................................................................15
PR 2...............................................................................................................................................15
(Task 1.3)......................................................................................................................................16
PR 2...............................................................................................................................................16
2

Assessment event 1
Question 1
Functions
Conditional formatting: It is mainly used in order to highlight the cells in terms of showing them
greater than & less than format, It helps us to find the average of out of the given value and apply
rules to different cells.
Data Analysis: It is a special Add ins available in File – Options – Add Ins – Analysis Tool Pak
[VBA]. Through this we can sort multiple calculations in excel with a proper formatting.
Cell Formatting:In order to Clear and apply border, check the font size and apply the desired one,
Apply the border on specific direction such as Right Border, Bottom Border etc. we take help of
Cell Formatting.
Formulas and functions: In order to solve different calculations such as Auto Sum, Financial,
Logical, Look up and references, evaluating the formula we use this tab.
Pivot table: In data summarizations through table we use an automated functions of excel which
is called as Pivot Table It can help you to understand the table with different headings inserted
easily.
Row Labels
Sum of
amount
direct expanses 20000
indirect
expanses 23000
sales 100000
Grand Total 143000
3

Charts: When it comes to the visualization of the data we use charts. These are tools that are used
for data summarization in which an individual can easily summarize data in the different heads.
1 2 3
0
20000
40000
60000
80000
100000
120000
particular
amount
Sorting: While we know that by selecting the specific data, it will become easy for us to answer
the query, sorting technique of excel sheethelps us in order to data identification through which a
data can be easily identified in a large group of data.
Filter: It is a feature of excel sheet which is a quite useful tool in data verification through this
tool an individual can easily verify data and scrub that out of the bunch as per the requirement.
Formula Auditing: When it comes to identification and validation of the Formula we need to
understand that the manual effort has to minimize and proper automated validation has to be
done. The formula auditing tool is providing evolution of formulas through which verification of
formulas can be measured.
Macros: In order to record a formula and run them on timely manner so that required answer can
be received we take help of Macros. We need not to formulate the sheet again and again. It
works as the CSS file.
Freeze panes: When we need to stabilize the cell to check the heading and move out on
remaining spread sheet we freeze it.
Split view: The split view is a technique of document rotation through which an individual can
see sheet in any direction.
Protect and share workbook: Through this tool an individual can protect their work by password
and certain instruction. While sharing we can also let the security applied so that no one else can
make the changes.
Get External Data: The way we import the data from one device to other device in order to
minimize the effort of copying and pasting effort. The excel sheet also provide ways through
which an individual can export outside data to extract in excel.
4

Data Validation: Suppose there is a list which needs to apply on a cell or sheet. In this situation
we prepare the list and provide the validation. This is a tool in which an individual can set
maximum and minimum range of data, if the data is entered behind that limit than that is not
validated.
What if analysis: This is to determinedifferent scenario’s wherein we require changes in
formulas. Apart from that we can also gather the information with “seek formula” of a particular
sheet or cell.
Step 2:
5

Question 2
PR 1, 2, 3 & 4
Cell alignment
The cell alignment feature enables to align the various cells. The cell can be aligned either
horizontally or vertically depending on the intent of the user.
6

Fonts
Font option can be used to the change the font style and font size of the content as per the
requirement of the intended user.
7

Borders
There can be left, right, all, outside, bottom along with top border. Also, there can be different
formats of border.
8

Styles
As the name suggest, cell style is used to give style to the cells. There can be different data and
model of the cells.
9

Formatting as table
There are three different types of formatting a table, light, dark and medium. Apart from that,
formatting can be customized as per the requirement.
10

PR 5
Page orientation
There are two types of orientation available in excel, namely, portrait and landscape. Such
orientation can be selected from the Page layout option.
11

Page margin
There are three varied types of margin out of which one can be selected, normal, wide and
narrow. Besides this, such margin can also be customized.
12