Introduction to Excel
Microsoft’s Excel is the most widely used business software in the world for data management, interpretation and analysis of data, and as a business modeling tool. Excel has valuable uses even if dedicated accounting or other software is used.
However, many users are only familiar with, and use a small percentage of Excel’s vast capacity. Learning more about Excel’s functions and how they can be applied to specific individual's or business’ needs can be a very valuable asset to both the individual and the business.
It is my aim to provide customized training to meet that need for individuals and businesses in Taranaki. This may be to learn a particular function or formula or to meet a specific workplace need or to provide more general up-skilling in Excel’s capabilities.
For those who want to work towards a formal qualification in Excel: Microsoft’s Excel Specialist qualification is an option or The International Computer Drivers’ License has modules in both basic and advanced Excel.
Suggested training topics for Excel (See also the workshops in the Menu.)
Training is tailored to clients' needs and can be at any level from beginner to advanced.
The following are examples of topics that may be of interest:
Managing worksheets and workbooks:
Creating workbooks/worksheets, using templates, protecting the structure and contents of worksheets/workbooks, customizing the ribbon/Quick Access Toolbar, copying and linking data between workbooks, importing data from external sources, custom formatting, cell styles and themes, Fill/Autofill/Series Fill/Flash Fill, GoTo/GoTo Special, Sort and Filter/Custom Sort, Paste Special.
Tables should form the basis of all your Excel work and have many advantages. Value and advantages, creating and updating, formatting/styles, naming tables/ cells/ranges, sorting and filtering, using structured formula references.
Allows users to determine and limit what data can be entered in cells. Input messages and error alerts can be added.
Order of operation, AutoSum, absolute & relative references, SUM(IF), COUNT(), COUNTA(), MIN(), MAX(), AVERGE().
Allows certain cells to be highlighted or “stand out” with colourful or other specific formatting if certain conditions are met. Built-in formatting allows formatting for: top/bottom values, above/below average, greater/less than, text/date/duplicate values, data bars, color scales, icon sets and, at a more advanced level, the use of formula.
Summarising data from different worksheets/workbooks.
Formula and data from more than one workbook can be linked allowing for automatic updating.
Some advanced formula and functions: (Excel has over 450 functions)
Lookup functions: Excel can perform various logical and look-up functions to help find and interpret and analyse data. Lookup functions include: LOOKUP(), VLOOKUP, HLOOKUP, MATCH and INDEX.
Logical functions: include: IF, AND, OR and NOT, IFS and can be nested (combined) to form powerful “decision-making” formula.
Statistical procedures can be performed by using SUMIFS, AVERAGEIFS and COUNTIFS.
Formula editing and error checking.
Workbooks can be shared either on a network, allowing more than one user at a time, or each individual can work on a separate copy of the workbook which can then be merged to update.
Can be added to cells to document or remind others of important facts about the data.
Charts allow data to be displayed in a visual way. They can be dynamic allowing for forecasting and data modelling and they form the basis of Dashboards. A variety of chart types are suitable for different types of data and purposes. Charts can be formatted to allow for different themes and styles, a secondary vertical axis, they can be dynamic so update automatically, can show trendlines, etc.
Excel dashboards provide a snapshot or summary of a table of raw data. They provide an excellent way for a business to quickly and easily get an overview of their data and KPIs. They are dynamic and adjust to show different data depending on what the user selects. Dashboards usually use Pivot Tables, Slicers and Charts.
Date and Time functions:
Dates and times can be used in formula to determine overdue invoices, project scheduling, working hours etc. Commonly used date functions include: TODAY(), NOW(), DATE(), DATEVALUE(), WEEKDAY(), DAY(), MONTH(), YEAR().
Common functions include: Proper(), Lower(), Upper(), Trim(), Len(), Substitute(), Find(), Left(), Right().
What if… analysis and financial functions:
By using GOAL SEEK, and SCENARIOS data can be manipulated to test various business hypothesis while the financial functions allow you to calculate loan payments, depreciation etc.
Macros and an introduction to VBA (Visual Basic for Applications):
Macros allow repetitive tasks and actions to be automated, saving time and reducing errors. For example: to set up and format worksheets and reports, analyse data, create invoices, update records, insert text that is repeated often and repeat common actions in several worksheets or workbooks. Macros are stored as VBA which is Microsoft’s programming language.
Pivot Tables and Pivot Charts:
Pivot tables are extremely valuable tools for businesses as they allow for the analysis, summary and visualization of large amounts of data.
Data Model - Power Pivot and Power Query:
A new downloadable add-in for 2010 and 2013, and a permanent feature from 2016, Power Pivot and Power Query (called: Get & Transform from 2016), allow for data modeling for large amounts of data and provide many of the functions of a database programme such as Access: you can establish data models, relationships between tables, and create calculations. Power Query (Get & Transform) gives added capability in that it allows data from multiple sources (e.g. internet, Excel, Access, CSV files, text files, external databases SQL etc.) to be downloaded and then transformed (by adding or deleting columns, cleaning data and adding calculations). The data can then be analyzed in an Excel table, Pivot Table or added to a Data Model.
Add fun to your Charts by using pictures in place of normal bars or columns. This Chart shows car sales for the first 9 months of the year and then includes a Linear Trendline to predict sales for Oct, Nov & Dec
This is an example of a simple
Excel Dashboard which provides a snapshot or summary of a Table of raw data. In this case visitor numbers to NZ provided by Stats NZ. The Dashboard is dynamic in that by selecting one or more of the months on the Slicer, the Charts will adjust to reflect the data for those months. Dashboards provide an excellent way for a business to quickly and easily get an overview of their data.
The following chart I have created compares the number of weeks of maternity leave on full pay in various OECD countries. The Chart is a Mirror Bar Chart which shows two sets of data side by side with a common axis. (Raw data source: OECD). This type of Chart can serve many useful purposes in business and can be included in my course on Charts.