What is Excel?

Microsoft Excel is a powerful spreadsheet application widely used for data organisation,
analysis, and visualisation. With features like pivot tables, formulas, charts, and data
manipulation tools, Excel is essential in business, finance, education, and research. Its user-
friendly interface and vast functionality make it suitable for both simple tasks and complex data
analysis.

Why use Excel?

• Easy to learn: With an intuitive interface, Excel is accessible to beginners.

• Advanced features: Excel supports calculations, data analysis, pivot tables, and
charts.
• Extensive functions: Excel provides functions for statistical, financial, and logical
operations.
• Integration: Excel integrates well with other Microsoft Office applications and supports
importing/exporting data from multiple formats.

Top tips to get started with Excel:

• Learn keyboard shortcuts: Shortcuts can save time. For example, “Ctrl + C” for copy,
“Ctrl + V” for paste, and “Alt + =” to quickly insert the SUM function.
• Use named ranges: Name specific cells or ranges to simplify formulas and improve
readability.
• Leverage templates: Excel provides templates for budgeting, calendars, inventory
tracking, etc., to give you a head start on various tasks.
• Understand cell references: Using “$” to lock rows or columns (absolute references) is
important when working with formulas across cells.
• Explore PivotTables: PivotTables are powerful for summarising data and creating
custom reports.

Page 1 © Sage Publications, 2024

Getting started

Excel is part of Microsoft Office 365. Office 365 subscriptions give access to Excel, Word,
PowerPoint, and more. You can download it from [Link]

Many universities provide free access to Office 365, so check with your institution.

After downloading, follow the installation steps and sign in with your Microsoft account.

Open Excel from your applications list or by searching “Excel” in the start menu.

It is recommended to set Excel up for data analysis by activating the Data Analysis ToolPak
which enhances Excel’s data analysis features. Go to File > Options > Add-ins. Select Analysis
ToolPak and click Go. Check the box and click OK to activate.

“Solver” is another add-in which is useful for working with constrained optimisation problems,
although this is not covered in this course.

Page 2 © Sage Publications, 2024

A simple example

Let’s work through a basic example where we enter sample data, calculate descriptive
statistics, and save the results.

1. Entering data:

Open a blank Excel workbook.

In Column A, enter data, such as:

2. Calculating descriptive statistics:

Use Excel functions to calculate the mean and standard deviation.

In an empty cell, type =AVERAGE(A1:A5) to get the mean.

In another cell, type =STDEV.S(A1:A5)” to calculate the (sample) standard deviation.

Excel will automatically calculate and display the values.

Page 3 © Sage Publications, 2024

3. Saving your workbook:

Go to File > Save As to name and save your file in your preferred location.

You can also export to PDF by selecting Save As and choosing PDF as the file type.

Importing data

Excel supports importing data from several sources, such as CSV files, databases, and online
data sources.

Importing a CSV file:

• Go to the Data tab and select Get Data > From File > From Text/CSV.
• Browse to find your CSV file, then click “Import”.
• In the import window, click Load to bring the data into Excel.

Importing from an Excel file:

To import data from another Excel workbook, go to Data > Get Data > From Workbook. Select the
file you wish to import, preview it, and then click “Load”.

Page 4 © Sage Publications, 2024

Connecting to online data:

Use Get Data > From Web to import data from a URL.

Enter the URL and click OK. Excel will load the web page data into your workbook.

Using Excel as a calculator

Excel can perform both simple and complex calculations directly in cells.

1. Basic arithmetic operations:

In any cell, type “=5 + 3” and press Enter. Excel will display “8”.

Similarly, use “=5 – 3”, “=5 * 3”, or “=5 / 3” for subtraction, multiplication, or division.

2. Using functions:

Excel has built-in functions for mathematical operations, for example:

• SUM: “=SUM(A1:A5)” adds up all values in cells A1 through A5.

• SQRT: “=SQRT(25)” calculates the square root of 25.
• POWER: “=POWER(5, 2)” calculates 5 to the power of 2 (i.e. 25).#

3. Relative and absolute references:

When using formulas across cells, you can use relative references (for example, “A1”) or
absolute references (for example, “$A$1”).

Absolute references are useful when you need a fixed reference in a formula.

Page 5 © Sage Publications, 2024

Using Excel to create simulations

Simulations in Excel can help generate random data or model real-world scenarios.

1. Generating random numbers:

• Use the “RAND()” function to generate a random number between 0 and 1.

• For a random integer within a range, use “RANDBETWEEN”, specifying the lower and upper
bounds. For example, =RANDBETWEEN(1, 100) generates a random integer between 1 and
100.

2. Running a Monte Carlo simulation:

Let’s say you want to simulate 100 coin flips to see the distribution of heads and tails.

In Column A, use the formula “=IF(RAND()<0.5,"Heads","Tails")” and drag it down 100 rows.

Then, use “COUNTIF(A1:A100,"Heads")” to count the occurrences of heads.

Page 6 © Sage Publications, 2024

Visualising data

Excel’s visualisation tools can help turn your data into charts and graphs.

1. Creating a simple chart:

Select your data, go to the Insert tab, and choose a chart type (for example, Column, Line, Pie).

Excel will generate the chart based on your selected data.

2. Customising charts:

Use the Chart Design and Format tabs to adjust chart elements like titles, axis labels, colours,
and legend.

3. Conditional formatting:

Conditional formatting can highlight data patterns.

Select a range, go to Home > Conditional Formatting to apply rules like colour scales, data bars,
or icon sets.

Page 7 © Sage Publications, 2024