Microsoft Excel Spreadsheets are digital work spaces that allow users to manipulate data and generate summaries in graphical format. The goal of this module is to give skills to learners on how to interact with Microsoft Excel to solve various needs of an organization.
A spreadsheet is an application that allows calculations and generation of advanced graphs and charts from the data to be carried out efficiently. MS Excel is one of the most common spreadsheet applications. In Spreadsheet applications, each document is known as a Workbook and contains a worksheet.
Module Learning Outcomes
At the end of the module, learners will be able to
- Enter data in worksheets and navigating worksheets
- Use formulas and functions to get data summaries using formula libraries
- Enhance worksheets by formatting cells and worksheets
- Create theme based chart to give a visual feel of data
- Edit worksheets in preparation for printing with page setup
- Get data from external sources
- Use filters and grouping to organise and summarise data
Session 1A: Introduction to Spreadsheets
Learning Outcomes
By the end of this session, you will be able to:
- Launch the various spreadsheet tools available in the market (MS Excel and google sheets)
- Describe the various menus and tools available for use
- Work with the cell, rows and columns (Add and delete rows)
- Enter and align data in a cell
Session 1B: Working with cells and basic formulas
Learning Outcomes
The Merge capability is a common tool used to join selected cells into one or separate joined cells into single cells. The cells to be merged must be adjacent to each other. When merging, only the contents of the leftmost column are retained.
Session 1B: Working with cells and basic formulas
Learning Outcomes
The Merge capability is a common tool used to join selected cells into one or separate joined cells into single cells. The cells to be merged must be adjacent to each other. When merging, only the contents of the leftmost column are retained.
Session 2A: Managing worksheets and user interface
Learning Outcomes
The default name for Excel worksheets is Sheet1, Sheet2,….Sheetn. When working with multiple sheets of data, it is necessary to give meaningful names for easier access and reference. It is important to remember that:
- A worksheet name must be between 1-31 characters long.
- Two worksheets in the same workbook cannot have the same name.
- Special characters are not allowed in a worksheet name.
Session 2B: Managing advanced spreadsheet user environment
Learning Outcomes
- When working with large volumes of data, it is possible to view and compare this data either by opening it in a new window or by splitting a worksheet into different panes. It is possible to open multiple windows for a single workbook at the same time.
- When viewing multiple workbook windows, it is possible to arrange them in different ways to that different parts of a worksheet are visible.
- Once the windows are arranged in the preferred format, click the window you want to view to activate it.
- When working with large amounts of data, it is possible to split panes to lock rows or columns in different areas to compare data.
- Class Files: OlympicStandings
Session 3A: Basic formulas and Cell Referencing
Learning Outcomes
Formulas are used to perform calculations on numeric data in cells. All formulas begin with an equals sign (=). Formulas can be applied to:
- Cell values for instance where small amounts of data are being computed, you can use =C1+C2+C3
- Cell range (adjacent or non-adjacent cells), using =SUM(C1:C40)
Whenever the value in the cells is changed, results are automatically recalculated. - Functions: The PI() function, returns the value of pi: 3.142.
- References: A2 returns the value in cell A2.
- Constants: Numbers or text values entered directly into a formula, such as 2.
- Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.
- Class Files: Products ; ExamRecords
Session 3B: Creating and Formatting Charts
Learning Outcomes
Spreadsheet applications provide an excellent tool for creating charts.
They offer different types of charts to represent different types of information in a graphical format.
- Column Chart-compare values across categories
- Line Chart– display trends over time
- Pie Chart– Display contribution of each value to a total
- Bar Chart– shows comparisons among individual items
- Area Chart– emphasizes the magnitude of change over time and draw attention to a trend.
- Scatter charts– show the relationship in several data series plotted as X-Y coordinates. Mostly used to display statistical and engineering data.
- Bubble Chart– type of scatter chart where the size of a bubble represents the value of a third variable
- Stock Chart– show the fluctuation of stock prices or scientific data
- Surface Chart– find optimal combinations between two sets of data
- Doughnut Chart– can contain more than one data series and shows the relationship of parts to a whole.
- Radar Chart- compares the aggregate values of several data series
- Class Files:
