Comprehensive Report on Excel Features and Functions

This report provides a detailed analysis of various features and functions within Microsoft Excel. It begins with an introduction outlining the report's structure and objectives, which are to assist a colleague in an affiliated university. The report covers several key areas, including the use of If statements for decision-making, naming cells and ranges for formula clarity, absolute referencing for fixed calculations, lookup tables for data retrieval, pivot tables for data analysis and summarization, macros for automating repetitive tasks, and VBA language for creating user-defined functions. It also discusses the advantages of automated workbooks, strategies for improving workbook design, and the benefits of additional Excel features. The report emphasizes Excel's capabilities in data management, analysis, and automation, highlighting how these features can enhance efficiency and decision-making in business contexts. Furthermore, it discusses the importance of proper workbook design, including formatting, layout, and the use of appropriate functions to optimize performance and data security. The report concludes by emphasizing the extensive range of Excel's built-in and add-in functions, highlighting their power in calculations, data manipulation, and advanced analysis.

Table of Contents
Page No
Introduction
Task One
Task Two
Task Three
Task Five
Task Six
a) If Statements
b) Naming Cells and Ranges
c) Absolute Referencing
d) Lookup Tables
e) Pivot Tables
f) Macros
g) VBA Language
h) The Advantages of Automated Workbook
i) The Improvement of the Workbook
j) Benefit of Additional Features
List of Tables
References

Introduction
This report is consisting of six tasks and is aim at to help a colleague in an affiliated University
that outsources lecturers as consultants. This report in task one will create a model that calculates
the number of staff, the total hourly rate, also by using a Lookup statement automatically return
the position of each staff, and will create a pivot table to extract relevant and meaningful
information of choosing; in the task two it will calculate the departmental fixed costs on an
hourly basis; in the task three will use a lookup function to determine the hourly rate for each
staff member on the estimate; in the task four it will modify the formulae & functions of the
original model; task five will be about the embedded VBA editor, macros; and at the last the task
six will be about the report related to the important features of excel.
Task One
Task Two
Task Three
Task Five
Task Six
Microsoft excel posses the many feature and option that help the user in decision making, data
management with its many data and management features. Its data organization features helps to
summarize, sort, outline, and filter the data that could help to focus on the specific data, the
validation features make the data reliable by correcting in useful format. Different build in
functions and formulas of excel make the calculations process easy. A spreadsheet is consisting
of columns (vertical) and rows (horizontal) that make the matrix and columns. Basically, the
excel work sheet were designed to facilitate the accountants, sale executives, bankers, students,
etc to work with huge data, and figures. Usually, in excel has 11 categories of 365 functions
along with addition more addins that also empower the spreadsheet, the report will discuss the
few of the facilities and functions of the Excel:
k) If Statements:
Like other many useful functions in the Excel If statement has crucial importance as this function
is important in the decision making context. This function is a logical function that check the
required conditions, criteria, or used when the user has to test the something or any criteria,
show one result when the test result is True and otherwise different result when the test result is
False. Normally, in the If stamens there are three arguments, which could be entered simply as:

IF (logical-test, {value-if-true}, {value-if-false})
The logical-test is required to represent the value or any other expression that has to verify for
True or false, “value-if-true” is the value that the user want to be devolved if the logical-test
expression tested to True, “value-if-false” is the value that the user want to be devolved if the
logical-test expression tested to False. This function could be used to test the more than one
argument in nested order. The “If functions” could be used with logical operators like “AND”,
OR and also with the math operators to evaluate the more complex logic in the expression
(Cheusheva, 2014).
l) Naming Cells and Ranges:
In the Excel, cell name or the name of rang, mention the concern cell or rang, that could
substitute the cell references or values, by assigning the descriptive name to cells or ranges, the
user could easily handled the cells or ranges in the formulation of formulas. By assigning the
name to any range make it easy to remember the purpose of formula. For example, as in the
project spreadsheet, cells that contains the hourly rate of the staff could be named as the “hourly-
rate” in this way, in the formula =SUM (hourly-rate) is much easy to understand than =SUM
(C5:C30), the first character of the name should be a letter, should not be same as the cell
address. By using the cell rang, the user could perform the functions like sum or average etc.
m) Absolute Referencing:
Normally, while working in spreadsheet, when user copy the formula that contains the cell
references, the cell references changes known as relative cell references. Absolute referencing
unlike the relative cell reference is the reference of specific cell or rang of cells that could not be
changed even with the change of the size; shape of the spreadsheet, or by coping to other cell. It
distinguish the location of cell or rang of cells used in any formulas, comprise of column letter
and row number as in normal cell reference but each number and letter premised by ($) sing. The
major use of the absolute reference is in the formulas when the user wishes to fix the relative cell
reference. By using the absolute reference in the formula, the original formula never affected. As
in the project spreadsheet for example, in the staff hourly rate when we copy across the cell
=sum (C5:C15) it will change to =sum (D5:D15) in relative reference but when we put the $ sing
as =Sum ($C$5:$C$15) it will not change when we copy across the cell.
n) Lookup Tables:
By using the lookup function the user could easily lookup the specific value within the given
data table or data set. This task could be performed by using the query or formula by using the
syntax and by specific the data range. Excel contains the VLookup (vertical lookup function),
HLookup (horizontal lookup function), Match, Index, and Choose built in functions to make a
lookup table. A lookup table includes the values, the user want to look up and its translation; it
may be on the same worksheet. During entering the data at least one column should have unique
value that could distinguish the that row or items from other rows or items For example in the
project there are information about the staff in different columns and on more than one sheets,
lookup function in this case help to find the required information from any sheet about the any
staff member.

o) Pivot Tables:
Excel worksheet enables the user to store, explore, analyze, summarize and organize the data
from large tables of data without formulas. Normally, in the Excel the configuration of the table
is not easily altered. The feature of pivot table in the excel enables the user to reconfigure,
redisplay the data and allow to extract the required data from large data, this function induce the
dynamics in the data, user could easily reorganize and filter the data. Pivot table could be
formulated on the sheet or one separate sheet pivot table is a user friendly function, by drop and
drag the user could move, and pivot the data from one location to other. Useful for large data,
could use to subtotal data, used to transpose the data, used to grouping the data, filtering and
sorting the data.
p) Macros:
While working on Excel, the user often perform the various tasks frequently that need many
steps to achieve the required goal, the feature of macros in the Excel provide the user to make the
macro function by recording the series of actions by using the VBA, the recorded macros will
perform the steps for the user. The user could run, edit, or delete the created macro as needed.
The user has to decide the process which he wishes to automate while creating a macro, once the
macros has been created, the process of modification of the macros is easy, simply delete the
existing macro and re-record again according to the situation. By using the handy macros tools
the user could perform the repeated tasks rapidly, for instance entering text blocks, to use the
macros no need of high tech competency, just need to record the required actions that will be
saved as a macro. Macros could be display by using the Visual Basic Editor, also the user could
use the Quick Access Toolbar buttons to run the macro, and the macros could be run with
opening of the workbook by using the Auto Open option.
q) VBA Language:
Visual Basic for Applications (VBA) is the powerful language of programming, enables the user
to build the user-defined functions in excel. It could be used to control the various aspects of
other host programs, it is used to getting the tasks done easily and quickly, induce the automation
in the excel, as name depict that this is related to the Visual Basic, build in excel and run with
host program. VBA macros created to secure the data, as it prevent from the unscrupulous,
harmful programs to write viruses. In excel many built in function need the VBA, user could use
VBA language to use the many built in functions to accomplish the complex tasks. By using the
VBA program user could also easily use the functions in VBA of variables, message box (pop up
message), input box, conditional, logical operators, looping, etc.
r) The Advantages of Automated Workbook:
Microsoft Excel spreadsheet is much useful in daily use, especially in business matters, it
provide the user with much powerful tools to handle with the data, by using its advanced
functions and features the user could make the process automated that include the functions like
charts, graphs, diagrams, macros, VBA, lookup functions, pivot tables, mathematical and logical
operators, and many others which induce the advantages as:
 Help in decision making by managing, manipulating, analyzing the data.
 Induce the efficiencies.
 Helps to build own database.

 Provide the option to effective and easily comparison.
 The analysis of the large data is easy by filtering and sorting tools (pivot table).
 Working together with other user is easy.
 Visualization of the data set makes the decision making easy.
 With automation the customization is also useful.
 Enhance the availability of functions.
 Enhance the security of the data.
 Time saving, by macros, while entering the repeated data.
s) The Improvement of the Workbook:
When the user is going to work on the excel worksheet, the user could improve the
worksheet by pre use planning about the formatting, layout, design of the spreadsheet. Poor
design of the work sheet could slow the speed of recalculation. During the designing of the
work sheet the user should focus on the style, and template of the worksheet, the user should
avoid the use of volatile functions, should avoid the use of same formula repeatedly in more
cells, the user should use the simple formula in multiple cells not complicated formulas in
single cells and use consistent color and schemes. The user should place everything in single
worksheet. Always, protect the worksheet, and hide unnecessary work sheet or row or
columns; the use of the data validation functions should be use with care to enhance the
security of the data. The size of the row and columns should be according to the data size,
use one font, and make alignment of the data for better look.
t) Benefit of Additional Features:
Usually, in excel has 11 categories of 365 basic functions along with addition more addins that
also empower the spreadsheet of the Excel. Basic functions mean built in hundred of functions,
with having facility to add more functions. The user could see the list of function in functions
library that contains about the other functions. Normally the user use the few of the function in
the daily operations of data, but the use of other additional functions could make the work more
easy by providing the power of calculation, recalculation, and by proving the many other features
of functions in relation to financial, statistic, logical, math, Hyperlinks, Date and Time
Functions, Goal Seek function, etc. for example, Goal Seek function lets you set a formula cell to
achieve a desired result by changing the value of one of the variables in the equation. By using
the additional features of the worksheet, the user could create the user defined functions for
advanced mathematics and for text manipulations, for complex math, mega formulas, etc.
References
Cheusheva, S. (2014, November 26). Using IF function in Excel: formulas for numbers, text, dates, blank
cells. Retrieved April 14, 2014, from ablebites: https://www.ablebites,com/office-addins-
blog,2014/11/26/if-function-excel/

Excel IF Function . (2014). Retrieved April 12, 2015, from Contextures:
www.contextures.com/xlfunctions06_excel-if-function.html
Flye, C. D. (2007). Microsoft Office Excel 2007 Step by Step. Washington: Microsoft Press.
Ragupathi, K. (2012). Formulas and Functions with Excel. National Univerisity of Singapor.
Remenyi, D., Onofrei, G., & English, J. (2009). An Introduction to Statistics using Microsoft Excel.
Academic Publishing Limited.
Visual Basic for Applications. (2015, March 28). Retrieved April 19, 2015, from wikipedia:
en.m.wikipedia.org/wiki/visual_basic_for_applications