Spreadsheets

The first 8 lessons in the left column about using spreadsheet software have been created to illustrate some of the more complicated uses of spreadsheets that may be required as part of the NVQ assignment for this unit. The basic spreadsheet lessons on the right hand side have been made as an introduction to spreadsheets and as revision for the ICT Functional Skills exam.

Alternatively further down the page are a range of guides to other aspects of using spreadsheet software.

Spreadsheet Lessons

How to Create a Pivot Table

Overview A Pivot Table can be used to summarise large amounts of data using specified calculations and formats. It is called a Pivot Table because the headings can be rotated around the data to view or summarise it in many different ways. In this example, you will learn how to create a Pivot Table based… Read More »

0 comments

Password Protect a File

It is quite simple to protect documents in Microsoft Office applications; this same process can be used in most Office tools and the AES encryption will keep your information secure so long as you can remember your password.

0 comments

How to Record a Macro

Overview Macros are useful time savers, they can help to easily automate tasks you carry out in Excel, such as adding formatting to cells. In the example below, we are going to record a macro that will add currency formatting with 0 decimal places. We will record the macro on the Room Hire column and… Read More »

0 comments

Conditional Formatting

Conditional formatting is the process of changing the formatting of the contents in a cell depending on the values of the cell, for example, if a cell shows a positive number like £548.37 it will appear black, however if it was a negative number like £397.15 it would appear red automatically. Conditional Formatting Exercise: Open… Read More »

0 comments

How to Apply Automatic Subtotalling

Overview Automatic subtotals are useful when summarising data contained in a list. Subtotals are created using an Excel summary function such as SUM, COUNT or AVERAGE. To successfully use subtotals, it’s important that the data is organised in a properly designed list and sorted according to the column by which you want to summarise the… Read More »

0 comments

How to Use the TRIM Function

Overview The ‘TRIM’ function is useful to remove unwanted spaces before data in cells, as in the example below. There are spaces before the text in the ‘Course’ column that need to be removed, we will insert a function into the ‘Course Name’ column that takes this data and removes the trailing blanks. Step 1… Read More »

0 comments

How to Use the CONCATENATE Function

Overview The ‘CONCATENATE’ function is useful to combine data from different cells into one cell. Here, we will take the First Name and Surname from columns B and C and incorporate both sets of data into column A (i.e. to display the Full Name). Step 1 Click in cell A2, then click on ‘Insert Function’:… Read More »

0 comments

How to Use the VLOOKUP Function

Overview The VLOOKUP function (which stands for Vertical Lookup), is used to find specific information that has been stored in a spreadsheet table. In the example below, we are going to use an employee number to lookup the corresponding first name in another part of the workbook. Below is the spreadsheet page we wish to… Read More »

0 comments

Spreadsheet Basics

Financial Decisions

Let’s compare some bad decisions with money, to good financial advice. In this lesson we will be exploring some different aspects of finances that affect young people.

0 comments

Spreadsheet Basics

These exercises have been developed to give you a basic introduction to spreadsheets and to remind you of a few of the most basic principles used in spreadsheets, such as importing data, making charts or graphs and basic formula. Basic Data Handing and Formula; Clothing Store Open a new spreadsheet. Enter your name using text… Read More »

0 comments

Logical Formula

Logical formula are basically tests; and the results of that test will determine what will happen next. This lesson has been put together to help you better understand logical formula and practice their usage in a range of different ways. The slideshow below gives four different examples of logical formula, though the first two examples… Read More »

0 comments

Sorting and Filtering

Sorting and filtering are two ways or organisation data on a spreadsheet or database in order to make it easier to analyse and use. Sorting is the process of putting all the data in a particular order, whereas filtering allows you to turn your headings into drop down menus to display only the information you… Read More »

0 comments

Conditional Formatting

Conditional formatting is the process of changing the formatting of the contents in a cell depending on the values of the cell, for example, if a cell shows a positive number like £548.37 it will appear black, however if it was a negative number like £397.15 it would appear red automatically. Conditional Formatting Exercise: Open… Read More »

0 comments

More tips and lessons about using spreadsheet software

Vlookup function

The video below shows information relating to the Vlookup function:

0 comments

Adding a graph

The video below shows how you can use the data in your spreadsheet to create a graph:

0 comments

Create pivot tables

The video below shows how to create pivot tables.

0 comments

Subtotals in Excel

The videos below show how to create subtotals in Excel spreadsheets.   Share your thoughts We’d really like to know what you think about the video, please leave your comments below.

0 comments

Data Table

The video below shows how to create a single – data input table

0 comments

Working with Columns

The video below shows how to work with columns.

0 comments

Sorting column data alphabetically & numerically

The video below shows how to sort column data alphabetically and numerically.

0 comments

Average Function

The video below shows how to use the average function.

0 comments

The Excel Count Function for Numbers and Blanks

The video below shows how to use the Count function

0 comments

SUM Formula

The video below shows how to use the SUM formula.

0 comments

Multiplication in Excel

The video below shows how to create a simple multiplication formula:

0 comments

Subtraction in Excel

The video below shows how to create a simple subtraction formula:

0 comments

Microsoft Tutorials: Charts

The links below show you how to create charts:

0 comments

Microsoft Tutorials: Pivot Tables

The links below shows you how to create pivot tables:

0 comments

Microsoft Tutorials: Formulas

The links below show you how to create formulas:

0 comments

Microsoft tutorials: Macros

The links below show you how to create macros:

0 comments