Excel and VBA: Working with Data Efficiently

The Excel courses I took were invaluable.
Laurent, BSc Anthropology

Our popular Excel and VBA practical workshops will resume in week 3 of Michaelmas term 2021. You will work with fellow students or colleagues, utilising your prior experience, web searches, and in-application Help features to find the solutions to real-world problems. 

Workshops will take place online and in person throughout the year. Please note that the content for the online and in-person workshops are the same. Unless (online) shows up in the workshop booking link, the session will take place on campus in LRB.R.08 in the lower ground floor of the Library. 

Click on the links below to book your place or express an interest so you are notified as soon as a new session is scheduled. 

 

Excel

Excel 1: Calculated Worksheets (Workshop)

Prerequisites: none 

Whether you are completely new to Excel or are self-taught and looking for a refresher, this course is for anyone. You will cover basic functionalities in Excel by creating a budget, including tasks such as: 

  • Creating basic calculations (addition, subtraction, multiplication and division) 

  • Copying cells/formulas efficiently 

  • Essential good practice principles to keep your Excel sheet clean and easy to use 

Click on the link below to check availability and book your place:

Excel 1: Calculated Worksheets   

Excel 1: Calculated Worksheets (online) 

Excel 2: Numeric Calculations (Workshop)

Prerequisites: Excel 1: Calculated Worksheets

This workshop builds upon the fundamental skills learned in Excel 1: Calculated Worksheets. In Excel 2, you will learn to create more efficient formulas, through absolute cell referencing, and more efficient workbooks, by learning how to use formulas across different worksheets within one Excel workbook.  

Click on the link below to check availability and book your place:

Excel 2: Numeric Calculations   

Excel 2: Numeric Calculations (online) 

Excel 3: Essential Functions (Workshop)

Prerequisites: Excel 2: Numeric Calculations

Excel 3 is suitable for anyone who like to learn or refresh their skills regarding a wide range of basic functions. In this session, we will use functions to find the sum, average, maximum and minimum values within a dataset. You will also look at absolute cell referencing (first seen in Excel 2) to create flexible custom calculations. 

Click on the link below to check availability and book your place:

Excel 3: Essential Functions   

Excel 3: Essential Functions (online) 

Excel 4a: Lookup Functions (Workshop)

Prerequisites: Excel 3: Essential Functions

This workshop is an introduction to lookup functions. Lookup functions are used to examine data tables to extract specific information, and are widely used in professional environments (data analysis, building financial models). You will also cover conditional functions and using data across worksheets. This course is an important pre-requisite for Excel 5. 

Click on the link below to check availability and book your place:

Excel 4a: Lookup Functions   

Excel 4a: Lookup Functions (online) 

Excel 4b: Pivot Tables (Workshop)

Prerequisites: Excel 3: Essential Functions

Excel 4b is for anyone looking to learn the fundamentals of pivot tables. Pivot tables are widely used, including in a professional setting, to summarise and present large amounts of data as succinct tables, taking into account only the data that is relevant for your analysis. You will learn to use pivot tables not only to organise data, but also manipulate it by looking more in depth at various pivot table settings. 

Click on the link below to check availability and book your place:

Excel 4b: Pivot Tables   

Excel 4b: Pivot Tables (online) 

Excel 4c: Sorting and Filtering (Workshop)

Prerequisites: Excel 3: Essential Functions

In this workshop, you will learn about sorting and filtering. These functionalities of Excel are used to manipulate large tables of data: sorting will determine the order of the data, while filtering will manipulate what you see based on specific constraints you have set. 

Click on the link below to check availability and book your place:

Excel 4c: Sorting and Filtering   

Excel 4c: Sorting and Filtering (online)  

Excel 4d: Charting Data (Workshop)

Prerequisites: Excel 3: Essential Functions

In Excel 4d, you will learn how to visualize data sets by creating various types of charts and graphs. In this session, you will look at column charts, trend line predictions, pie charts, combo charts (charts combining multiple data sets), and scatter plots. You will also learn how to customize the look of the charts. 

Click on the link below to check availability and book your place: 

Excel 4d: Charting Data   

Excel 4d: Charting Data (online)  

Excel 4e: Data Tools (Workshop)

Prerequisites: Excel 3: Essential Functions

This workshop will teach you how to extract data from a table based on selected criteria, present predefined choices and reduce input errors. More specifically, you will learn how to create static and dynamic drop-down lists, display input and error messages and restrict invalid user inputs. 

Click on the link below to check availability and book your place: 

Excel 4e: Data Tools   

Excel 4e: Data Tools (online)

Excel 4f: What-if Analysis (Workshop)

Prerequisites: Excel 4a: Lookup Functions

This workshop will introduce you to What-if analysis. You will learn to use this type of analysis to compare different scenarios, perform reverse calculations (to see what is needed to achieve specific goals) and carry out sensitivity analysis (to examine the effects of variables on the same formula). 

Click on the link below to check availability and book your place: 

Excel 4f: What-if Analysis   

Excel 4f: What-if Analysis (online)  

Excel 5: Complex Calculations (Workshop)

Prerequisites: Excel 4a: Lookup Functions

In Excel 5, you will build on the skills that you learned in Excel 1-4a. This course will show you an example of advanced calculations using lookup functions commonly used in a professional setting. You will construct complex formulas that incorporate data from multiple tables by using nested functions. 

Click on the link below to check availability and book your place: 

Excel 5: Complex Calculations   

Excel 5: Complex Calculations (online)  

Excel 6: Using Macros (Workshop)

Prerequisites: Excel 5: Complex Calculations

Excel 6 will show you the fundamentals of macros and introduce you to VBA. Macros are used to automate repetitive tasks and can help you quickly organise large amounts of data. VBA is Excel’s programming language, and, in this task, you will use it to do basic macro troubleshooting and editing. 

Click on the link below to check availability and book your place: 

Excel 6: Using Macros   

Excel 6: Using Macros (online)  

 VBA

VBA 1: Elements and Variables (Workshop)

Prerequisites: Excel 6: Using Macros

VBA 1 is for anyone looking to learn the fundamentals of Visual Basic for Applications (VBA), Excel’s programming language. In this workshop, you will be introduced to some of the base vocabulary used in VBA, explore the layout of the VBA window, and set-up simple one-click functionalities to run tasks using VBA code and button creation. 

Click on the link below to check availability and book your place:  

VBA 1: Elements and Variables   

VBA 1: Elements and Variables (online) 

VBA 2: Worksheet Manipulation (Workshop)

Prerequisites: VBA 1: Elements and Variables

This workshop, like VBA 1, will help you understand some of the basic functionalities that VBA can automate. You will use VBA code to perform tasks such as renaming, protecting, hiding worksheets and saving workbooks.  

Click on the link below to check availability and book your place:  

VBA 2: Worksheet Manipulation   

VBA 2: Worksheet Manipulation (online) 

VBA 3: Data Manipulation (Workshop)

Prerequisites: VBA 2: Worksheet Manipulation

This workshop is for anyone looking to use VBA to automate tasks and manage large amounts of data. You will use VBA to detect and remove errors in a data set and to clean-up and reorganise the existing data. 

Click on the link below to check availability and book your place: 

VBA 3: Data Manipulation   

VBA 3: Data Manipulation (online) 

VBA 4: Debugging and Troubleshooting (Workshop)

Prerequisites: VBA 3: Data Manipulation

In this workshop, you will learn to detect and fix common errors in VBA code. This will help you troubleshoot, by showing you what to look for when VBA code doesn’t run as intended. 

Click on the link below to check availability and book your place: 

VBA 4: Debugging and Troubleshooting   

VBA 4: Debugging and Troubleshooting (online)   

VBA 5: Report Generation (Workshop)

In this workshop, you will learn how to automate the creation of a professional report on Word, based on data in an Excel sheet. You will utilise VBA commands (such as copy, paste, format and more) to transfer elements from Excel to Word, and apply VBA commands for formatting in Word. 

Click on the link below to check availability and book your place: 

VBA 5: Report Generation   

VBA 5: Report Generation (online) 


 

 

I'd thoroughly recommend using the Digital Skills Lab if you are unfamiliar with Excel and would like to learn how to use particular functions

Jasmine Prasad, Department of Management