Tutorials
Microsoft and other providers have quite a wide range of free online tutorials which are often overlooked by those seeking to improve their Excel skills. There are hundreds of free Excel tutorials available from Microsoft, so to make it easier, we’ve compiled a list of those which relate specifically to Financial Analysis.
Prior to coming on a Plum Solutions Financial Analysis workshop or online course, we recommend that you have at least a working knowledge of Excel. During the course we assume you are already using Excel as part of your work. For a recommended list of what you should know before coming on a course, please see the prerequisites.
Below is a list of Excel tools and functions you’ll find useful for modelling and analysis. Click on the links below to go directly to the tutorial on the host websites:
Basic Tutorials – Navigating in Excel
What is Excel
You should know this before coming on any of the Plum Solutions Training Courses.
With Excel on your PC, Mac, or mobile device, you can:
- Streamline data entry with AutoFill.
- Spot trends and patterns with data bars, sparklines, color coding, and icons.
- Get chart and PivotTable recommendations based on your data, and create them with one click.
- Share your work from within Excel, using OneDrive.
- Edit spreadsheets with others, wherever they are.
Excel Version: Any
Length: 1 minute
Level: Basic
Create a Workbook
You should know this before coming on any of the Plum Solutions Training Courses.
A workbook is a file that contains one or more worksheets, to help you organize your data. You can create a workbook from a blank workbook or a template.
Excel Version: Any
Length: 30 seconds
Level: Basic
Insert or delete a worksheet
In Excel, you can easily add, rename, and delete worksheets in your workbook.
Excel Version: Any
Length: 34 seconds
Level: Intermediate
Move or copy worksheets
This is handy, but you do not need to know this before coming on any Plum Solutions Training Courses.
You can move or copy a worksheet in the same workbook to organize your workbook exactly how you want.
Excel Version: Any
Length: 51 seconds
Level: Intermediate
Create or change a cell reference
You should know this before coming on any of the Plum Solutions Training Courses
A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.
Excel Version: Any
Length: n/a
Level: Intermediate
Insert or delete rows or columns
This is handy, but you do not need to know this before coming on most Plum Solutions Training Courses.
Small changes in the layout of your worksheet can give you big improvements in readability. Insert and delete rows, columns, and cells to organize your worksheet.
Excel Version: Any
Length: 1 minute
Level: Intermediate
Change the column width and row height
You should know this before coming on any of the Plum Solutions Training Courses.
You can manually adjust the column width or row height or automatically resize columns and rows to fit the data.
Excel Version: Any
Length: 40 seconds
Level: Intermediate
Freeze columns or rows
You should know this before coming on any of the Plum Solutions Training Courses.
Freeze rows and columns to keep specific areas visible when you scroll in a worksheet.
Excel Version: Any
Length: 1 minute 27 seconds
Level: Intermediate
Hide or unhide columns
Hide or unhide columns in your spreadsheet to show just the data that you need to see or print.
Excel Version: Any
Length: 37 seconds
Level: Intermediate
Move Cells
You can move cells in Excel by drag and dropping or using the Cut and Paste commands.
Excel Version: Any
Length: 57 seconds
Level: Intermediate
Copy Cells
Copy cells in your worksheet using the Copy and Paste commands.
Excel Version: Any
Length: 33 seconds
Level: Intermediate
Select Cell Contents
In Excel, select cell contents to help manage your work efficiently across a workbook.
Excel Version: Any
Length: 57 seconds
Level: Intermediate
Basic Tutorials – Basic Formatting
Format numbers in cells
You should know this before coming on any of the Plum Solutions Training Courses.
In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers.
Excel Version: Any
Length: 27 seconds
Level: Basic
Align text in a cell
You should know this before coming on any of the Plum Solutions Training Courses.
Use different alignment options to align text in a column, row, or workbook.
Excel Version: Any
Length: 40 seconds
Level: Basic
Apply styles
You should know this before coming on any of the Plum Solutions Training Courses.
Apply Excel Styles to make your cells stand out.
1. Choose cells to format.
2. Select an Excel Style to apply.
3. Select More for more options.
Excel Version: Any
Length: 1 minute
Level: Basic
Copy cell formatting
Copy cell formatting in your worksheet using copy and paste options, Format Painter, and dragging techniques.
1. Choose a cell to copy.
2. Select an Excel Style.
3. Select Format Painter and drag to apply the Style.
Excel Version: Any
Length: 1 minute
Level: Basic
Basic Tutorials – Introduction to Formulas
Create formulas
You should know this before coming on any of the Plum Solutions Training Courses.
Get started on how to create formulas and use built-in functions to perform calculations and solve problems.
Excel Version: Any
Length: 1 minute 39 seconds
Level: Basic
SUM
You should know this before coming on any of the Plum Solutions Training Courses.
Add values in Excel with the SUM function. You can add individual values, cell references, ranges, or a mix of all three.
Excel Version: Any
Length: 28 seconds
Level: Basic
Basic Tutorials – Basic Charts
Create charts
Charts help you visualize your data in a way that creates maximum impact on your audience. Learn to create a chart and add a trendline.
Excel Version: Any
Length: 45 seconds
Level: Basic
Add a chart title
Add a title to your chart to make it easier to understand.
Excel Version: Any
Length: 1 minute 13 seconds
Level: Basic
Show or hide a chart legend
Show a legend to add more context to your chart data, or hide it to give it a clean look.
Excel Version: Any
Length: 39 seconds
Level: Basic
Basic Tutorials – Starting with PivotTables
Create a PivotTable
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
Excel Version: Any
Length: 45 seconds
Level: Basic
Work with PivotTables
When you have a large amount of data, you may want to rearrange the data in your PivotTable to make it easier to work with. You can also add or change the fields in a PivotTable.
Excel Version: Any
Length: 1 minute 30 seconds
Level: Basic
Intermediate Tutorials
Working with cells
Create a drop-down list
Help people work more efficiently in worksheets by using drop-down lists in cells where they can pick an item from a list you create.
Excel Version: Any
Length: 51 seconds
Level: Intermediate
Split data into different columns
You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Excel Version: Any
Length: 51 seconds
Level: Intermediate
Combine data
You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function.
Excel Version: Any
Length: 1 minute 44 seconds
Level: Intermediate
Combine text from two or more cells into one cell
You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function.
Excel Version: Any
Length: 1 minute 44 seconds
Level: Intermediate
Create a list of sequential dates
You can quickly create a list of dates, in sequential order, by using the Fill Handle 
or the Fillcommand.
Excel Version: Any
Length: 53 seconds
Level: Intermediate
Auto Fill Dates
With the Auto Fill feature, you can automatically fill cells with data that follow a pattern or series.
Excel Version: Any
Length: 53 seconds
Level: Intermediate
Validate Cell
When you create worksheets that will be used by others, it’s important to make sure they can only enter valid data. Use Excel’s data validation features to make rules to restrict the type of data or values that others can enter into a cell.
Excel Version: Any
Length: 1 minute 18 seconds
Level: Intermediate
Create a custom number format
Create and build a custom numeric format to show your numbers as percentages, currency, dates, and more.
Excel Version: Any
Length: 57 seconds
Level: Intermediate
Conditional Formatting
Conditional formatting provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based on criteria you provide.
Excel Version: Any
Length: 58 seconds
Level: Intermediate
Formulas
IF
Use IF to return a value based on a condition.
For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
Excel Version: Any
Length: 57 seconds
Level: Intermediate
VLOOKUP
Use VLOOKUP when you need to find things in a table or range by row.
Excel Version: Any
Length: 53 seconds
Level: Intermediate
IFS
Use the IFS function to check whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
Note: This feature is only available if you have an Office 365 subscription.
Excel Version: Any
Length: 1 minute 17 seconds
Level: Intermediate
SUMIF
Use the SUMIF function to sum the values in a range that meet criteria that you specify.
Excel Version: Any
Length: 46 seconds
Level: Intermediate
SUMIFS
Use SUMIFS to test multiple conditions and return a value based on those conditions. For example, you could use SUMIFS to sum the number of a products sold by a certain salesperson.
Excel Version: Any
Length: 1 minute 60 seconds
Level: Intermediate
COUNTIF
Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
In its simplest form, COUNTIF says:
-
=COUNTIF(Where do you want to look?, What do you want to look for?)
Excel Version: Any
Length: 3 minute 51 seconds
Level: Intermediate
Define and use names in formulas
In Excel, you can name cells and cell ranges, and use those names in formulas. This is a useful feature that makes formulas easier to understand and maintain.
Excel Version: Any
Length: 1 minute 46 seconds
Level: Intermediate
Structured Reference Tables
Create a table
You can create and format a table, to visually group and analyze data.
Excel Version: Any
Length: 30 seconds
Level: Intermediate
Sort data in a table
Sorting is one of the most common tools for data management. In Excel, you can sort your table by one or more columns, by ascending or descending order, or do a custom sort.
Excel Version: Any
Length: 1 minute
Level: Intermediate
Filter data in a range or table
Use filters to temporarily hide some of the data in a table, so you can focus on the data you want to see.
Excel Version: Any
Length: 1 minute
Level: Intermediate
Add a Total row to a table
You can add totals to a table by selecting the Total Row checkbox on the Design tab. You can also add a function from the total row drop-down.
Excel Version: Any
Length: 34 seconds
Level: Intermediate
Use slicers to filter data
Convert data into a table
Excel Version: Any
Length: 36 seconds
Level: Intermediate
Charting
Analyze your data quickly
Excel’s Quick Analysis button lets you instantly create different types of charts, including line and column charts, or add miniature graphs called sparklines.
Excel Version: Any
Length: 37 seconds
Level: Intermediate
Change the source for a chart
If you need to change data in a chart, you can do it from its source.
Excel Version: Any
Length: 46 seconds
Level: Intermediate
Update the data in an existing chart
If you need to change data in a chart, you can do it from its source.
Excel Version: Any
Length: 46 seconds
Level: Intermediate
Add a trend or moving average line to a chart
Add a trendline to your chart to show visual data trends.
Excel Version: Any
Length: 23 seconds
Level: Intermediate
Add a secondary axis
Add a secondary axis to your chart to show different values than the original axis.
Excel Version: Any
Length: 47 seconds
Level: Intermediate
Use Sparklines to show data trends
Sparklines are mini-charts placed in single cells, to show visual data trends. You can quickly add and format a Sparkline chart in your worksheet.
Excel Version: Any
Length: 39 seconds
Level: Intermediate
PivotTables
Use the Field List to arrange fields in a PivotTable
After you create a PivotTable, you’ll see the Field List. You can change the design of the PivotTable by adding and arranging its fields. If you want to sort or filter the columns of data shown in the PivotTable, see Sort data in a PivotTable and Filter data in a PivotTable.
Excel Version: Any
Length: 1 minute 30 seconds
Level: Intermediate
Group data in PivotTables
PivotTables are a great way to summarize, analyze, explore, and present your data. You can even group the data in a PivotTable to enhance the layout and format of your PivotTable reports.
Excel Version: Any
Length: 1 minute 37 seconds
Level: Intermediate
Filter data in a PivotTable
Filter the data to focus on a smaller portion of your PivotTable for an in-depth analysis.
Excel Version: Any
Length: 36 seconds
Level: Intermediate
Create a PivotChart
Create a PivotTable to summarize and analyze your data in a structured format. PivotCharts are a great way to add data visualizations to your data.
Excel Version: Any
Length: 1 minute
Level: Intermediate
Share and Co-Author
Share a Workbook
Share a workbook with others, right within Excel. You can let them edit the workbook or just view it.
Excel Version: Any
Length: 41 seconds
Level: Intermediate
Add and review Comments
Add comments to cells to explain what the cells contain.
Excel Version: Any
Length: 1 minute 30 seconds
Level: Intermediate
Password-Protect Workbooks
Protect a workbook with a password to prevent others from adding, moving, deleting, hiding, or renaming the worksheets in the workbook.
Require a password to open an Excel file
Excel Version: Any
Length: 45 seconds
Level: Intermediate
Advanced Tutorials
Array Formulas
Guidelines and examples of array formulas
An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.
Excel Version: Any
Level: Advanced
Dynamic array formulas in non-dynamic aware Excel
In September, 2018 we introduced several new functions in a family of functions called dynamic arrays. These functions will automatically populate a range, or array, and eliminate the need for legacy Ctrl+Shift+Enter (CSE) array formulas. Dynamic array aware Excel does have performance implications with regards to how it recognizes legacy CSE formulas.
Excel Version: Any
Level: Advanced
Work with Macros
A brief introduction to Macros is given in the Advanced Financial Modelling course. You do not need to know this prior to coming on any Plum Solutions courses.
You want to automate a repetitive task in Excel so that you can do the task again with a single click. The best way to do that? Record a macro. And the macro recorder is the easiest way to create a new macro in Excel.
- The fastest way to automate a repetitive task in Excel so that you can do the task again with a single click is to record a macro
- See how to edit a macro in a special text editor called the Visual Basic Editor. Visual Basic is the programming language that macros are recorded in.
- To make sure your macro is always available on your computer, save it to your Personal Macro Workbook, which is a special hidden workbook that loads every time you start Excel.
- Link your macro to a button and make it available in two places: In a custom group on the Developer tab, and on the Quick Access Toolbar.
Excel Version: Any
Length: 4 videos; Around 14.5 minutes in total
Level: Advanced
Introduction to Modern Excel Tutorials
Introduction to Microsoft Power Query for Excel
Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, and more.
Excel Version: Any
Level: Advanced
Power Pivot: Powerful data analysis and data modeling in Excel
Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.
Excel Version: Any
Level: Advanced
Creating a Dashboard using Power BI
Power BI is one of those features in Office 365 that is a hidden gem. Power BI provides business analytics to analyze data and share insights. I’m going to go ahead and launch Power BI from the Office 365 portal. We have a fairly empty Power BI right now so let’s go ahead and add in some data. I happen to have a spreadsheet already created with the sales for the last several years for the hotels.
Excel Version: Any
Length: 4 minutes 33 seconds
Level: Advanced
In-House Training
Arrange an in-house training session for your organisation.
Face-to-Face Training
Attend a public workshop
Online Training
Enrol in an online course and get started straight away