Excel Workshop Series: Working with Data Efficiently

In this task, you will make a straightforward budget sheet for a company. By using different streams of income and expenditure, you will use core Excel formulae and functionalities to learn how to:

• Set up automatic addition, multiplication, division, subtraction
• Format for visual clarity
• Use fill handle (a smart copy-paste tool that helps you quickly fill in a series or pattern of data by dragging it across cells)
• Track visually cells that affect the value in the current cell
• Work with large amounts of text
• Prepare and format sheets for digital export or printout

This task will give you a solid grasp of Excel fundamentals and good practice in a concrete way, providing you with strong essentials to build on.

In this task, you will build on your learnings from Excel 1 and learn how to:

• Make your formulae more efficient
• Use cell locking (using the dollar sign $ to lock a cell reference, meaning when you copy a formula, the locked cell reference won’t change)
• Use cells across different sheets in your formulae
• Create tables with automatically updating columns
• Utilise mini charts (a visual taking up just a single cell to represent an entire row of data)
• Learn the essentials of chart creation

This task will cover a broad base of fundamentals, allowing you to practice what you learned in Excel 1, while providing you with the building blocks to learn more elaborate Excel functionalities.

In this task, you will use a fictive sales sheet from a company to deepen your knowledge of further Excel core functionalities, and learn how to:

• Learn functions widely used for descriptive statistics
• Copy formulae efficiently
• Use freezing panes (a feature to lock parts of your sheet so that they always stay visible when you scroll through datasets, no matter how large)
• Troubleshoot common error messages
• Use conditional formulae (formulae that perform actions based on specified conditions)
• Utilise conditional formatting to automatically highlight important data (functionality that changes the look of a cell based on specified conditions)

In this task, you will use lookup functions and expand on what you learned in previous sessions to help you fill in several fictive company payroll and performance sheets. You learn how to:

• Use lookup functions (functions that help you find and retrieve information from a table based on your criteria, like a search engine for your dataset)
• Build conditional formulae (formulae that perform actions based on specified conditions)
• Use data across different worksheets efficiently

In this task, you will learn to use pivot tables to achieve various goals, such as:

• Summarise and present large amounts of data succinctly
• Manipulate your data to customise the output of your pivot table
• Create new variables based on existing ones
• Create different views of your data, such as different pivot table formats or charts
• Understand the fundamentals of dashboard creation

In this task, you will learn various sorting and filtering tools, which are used to analyse data by:

• Sorting it – to help organise it into specific orders, which will then make it easier to find and work with the information
• Filtering it - to help you selectively display or isolate specific data in your spreadsheet while hiding the rest, helping you quickly focus on what matters most
• Utilising features that help create values from examples
• Using subtotals – to help create summaries and breakdowns within your data that will automatically calculate totals for specific groups or categories of information

In this task, you will learn to create various data visualisations (such as charts and graphs to help turn your data into easy-to-understand visuals, which will help you see trends, comparisons and patterns at a glance), such as:

• Summarise and explore graphically complicated data
• Compare visually values across categories
• Show trends over time
• Forecast based on available data
• Show proportions of the whole dataset
• Visualise hierarchical data
• Use scatter plots
• Show cumulative effect of positive and negative values
• Visualise mixed types of data 
• Visualise data across geographical regions

In this task, you will learn how to use data validation (which allows you to set rules for what can be entered into a cell, helping to ensure accuracy and consistency) to do the following:

• Create dynamic drop-down lists (interactive menus that change based on your selections, automatically updating to show you only what is relevant)
• Display input and error messages (messages that pop up when you enter data into a cell: the input message provides guidance or additional information, while the error message alerts you when there is a problem with your input)
• Restrict invalid user inputs into cells (entries that do not meet the required criteria you have set for a cell)

In this task, you will learn to use What-If Analysis (which allows you to explore different scenarios by changing input values to see how they affect your calculations or outcomes). Specifically, you will learn how to:

• Compare different scenarios to explore how they change your outcome
• Perform reverse calculations to see what is needed to achieve specific goals
• Carry out sensitivity analysis to help you understand how changes in specific input variables impact your calculations or results

In Excel 5, you will build on the skills that you learned in Excel 1-4a. Using an example of advanced calculations commonly used in a professional setting, you will construct complex formulae that incorporate data from multiple tables by using nested functions, and learn how to:

• Use lookup functions (functions that help you find and retrieve information from a table based on your criteria, like a search engine for your dataset)
• Build conditional formulae (formulae that perform actions based on specified conditions)
• Apply the above functions in conjunction with each other to extract data from multiple tables and across multiple sheets
  
  

In this task, you will build on the skills that you learned in Excel 4b. Using an example of working with multiple data sets, you will enhance your data analysis and reporting capabilities, and learn how to:

• Import and integrate large volumes of data from various sources
• Manipulate your data model to customize the output of your Power Pivot
• Create new measures based on existing ones using DAX (Data Analysis Expressions)
• Generate different perspectives of your data, such as different Power Pivot Table formats
• Understand the fundamentals of creating a data model
  
  

In this task, you will build on the skills that you learned in Excel 4c. Using several examples of sorting and filtering data using Excel functions, you will create more effective data analysis workflows, and learn how to:

• Identify unique values from a dataset, providing a dynamic array of unique values
• Automatically organise your data into specific order as it updates or changes, making it even easier to work with the information
• Create filters that automatically update to selectively display or isolate specific data in your worksheet as it changes, helping you to maintain focus on what matters most
• Create a dynamic search box in Excel, which can be a powerful tool for finding specific entries in your data

In this task, you will build on the skills that you learned in Excel 4b and 4d. Using a sample data set, you will create a dashboard to present and analyse data more effectively, and learn how to:

• Create interactive pivot tables that update based on user interaction
• Create dynamic charts and graphs that update based on user interaction, providing a more engaging way to present data
• Understand the principles of effective dashboard design to ensure your dashboards are easy to understand and use
• Explore complex datasets, identify trends and patterns, and make data-driven decisions

In this task you will use a fictive event bookings data that needs to be cleaned, then moved into an attendance sheet template, and learn how to:

• Record Macros (automate repetitive tasks) good practice and preparation
• Use sorting and wildcards (search shortcuts using characters (*, ?) in search criteria to represent unknown or variable parts of text) to clean your data ahead of recording
• Learn fundamental Macros troubleshooting in VBA (the coding language behind Excel)
• Clean data in Excel using Macros and VBA
• Create one-click GUI (Graphical User Interface) experiences in Excel using Macros and VBA
• Optimise VBA code for sequential running of multiple Macros