Espresso topics are 'bite' sized topics that are no longer than two hours in duration.

If you feel that you need longer on an individual topic or want a 'pic 'n mix' of multiple topics then request a Latte workshop​​​​​​​​​. If you can't see a topic of your choice, please get in contact so that we can discuss it.

SET UP THE EXCEL APPLICATION

Customise the Ribbon (the area where all the buttons live) and the small toolbar known as the Quick Access toolbar (QAT) which sits above and to the left of the Ribbon. Use Options to set up Excel.

BACK TO BASICS

Make sure you can:

• Add, edit and delete values in cells.
• Apply number, date and text formatting.
• Insert or delete cells, rows or columns.
• Insert or delete, rename, colour code worksheet tabs.

PRINT AWKWARD WORKSHEETS

Learn how to use the Page Setup, Header and Footer and Page Break tools. Obtain a sensible print out from large and awkward spreadsheets.

SIMPLE CALCULATIONS

Create basic calculations, use AutoSum and Extended AutoSum to SUM, COUNT, find the AVERAGE, highest (MAX) or smallest (MIN) number. Speed up your work by using AutoFill. Caclulate values that are either in a different worksheet or a diferent workbook.

CELL REFERENCES

What do those \$ (dollar signs) in formulas mean? Learn the various ways of using cell references in formulas. This module will cover:

• Absolute cell references.
• Partial absolute cell references.
• Range Names Use descriptive names such as Products, to simplify referring to complicated ranges of data such as C20:C30.

Why do I sometimes see formulas that look like this {SUM(A1:A10:B1:B10)}? Learn about array formulas.

CUSTOM NUMBER FORMATTING

Learn how to format numbers or text when you cannot find an appropriate format by creating Custom Number formatting.

SIMPLE CONDITIONAL FORMATTING

Use Conditional Formatting to format values based on a condition. For example, display all sales figures that have not reached their target values in red.

A function is a type of formula that helps the user to perform calculations more easily. Excel offers users a variety of pre-set functions organised into libraries. This module will teach how to use a function and explore some of the more common ones in each function library:

• Date and Time: manipulate date and time values.
• Logical/Math/Statistical: test if you are over or under budget, sum values based on conditions such as sum all values in Department 1.
• Lookup and Reference: return values in other locations based on another value, for example return the name of the employee where Employee ID = 0001.
• Financial: how much will you pay per month if you take out a loan of £10000 at 5% for 3 years?
• Text: change the case or find text based on criteria.
• Information: learn how to control worksheet error messages.

String formula: a string formula is a way of combining calculations and text in the same cell. For example, you might want to include the statement “John sold 20 calculators in January” in a cell, where 20 is the result of a calculation and not a number that you have entered as text.

Once you are familiar with using Functions as described above, conditional formatting is limited only by your imagination! This module will show you tips, tricks and ideas to really improve your use of conditional formatting.

KEEP WORKBOOKS ERROR FREE

Learn spreadsheet design good practices and keep worksheets error free with the use of check sums, error checking and Data Validation tools.

CREATE 2D AND 3D CHARTS

Learn the various chart types and how best to organise your data for the type of chart you are trying to produce. Then discover the tools to professionally format and display the chart.

INTRODUCTION TO MACROS

A macro is a way to automate a task that you perform repeatedly or on a regular basis. It is a series of commands and actions that can be recorded, stored and run whenever you need to perform the task. These tasks may be something as simple as inserting your name and address into a cell or more complex, such as reading all of the data from a file and entering it into a worksheet.
Learn how to create a simple macro and assign it to the Quick Access Toolbar or a shape such as a rectangle on the worksheet.

LINK WORKSHEETS TO WORD AND POWERPOINT

There are a number of ways to add information from Excel into either Microsoft Word or Microsoft PowerPoint. The first method is something you may do on a daily basis by simply copying the data from Excel and pasting it into Word or PowerPoint. The second method is by linking the information through a feature called Paste Special. Using this method will allow you to edit the data within Excel and it will automatically update the information in Word or PowerPoint.

WORK WITH LISTS AND TABLES OF DATA

Learn how to organise data. Take advantage of Excel database tools by converting lists of data into tables. Filter and sort data. Display Spark lines (in-cell charts) to show the trend of data within each row. Use Slicers to make filtering easier. Set up criteria to find how many invoices are overdue using Database functions.
Clean up data using formulas or Flash Fill (Excel 2013). Flash Fill is a new feature that really makes the task of grabbing just the bit of text that you want. For example, you may need just the email bit from this fully qualified address:customer/department=shipping@example.com.

EXTERNAL DATA SOURCES

Need to analyse data from a web page or other external source? Learn how to connect to data from differing sources such as:

• The Web
• Text files
• Access databases
• Other database sources

The Advanced Filter command enables you to find rows using more complex criteria, such as “Investments by Jones for more than £5000” or “Tax for Investments in Germany”, as well as extract information to a different location. Before using Advanced Filter, you must have set up a separate criteria range and entered the criteria required.

CREATE AUTO DATA OUTLINES

By creating an outline on a worksheet, you are able to quickly hide or display certain rows and columns of data so that you can see at a glance the most important information, or choose to see it in full. This can be useful for the purposes of printing different reports based on the same worksheet data, charting, consolidating, etc. For example, if you have a worksheet that contains an itemised expenses report for the month, you could break it down to just show the total weekly expenses or the total monthly expenses figures, rather than see the fully itemised report.

CONSOLIDATE DATA

When you consolidate data, you are able to create a summary for a number of data ranges either in the same or in different worksheets. For example, if you have a number of sales regions and you want to get an overall picture of how they are performing, you can get Excel to add together items with the same heading, even though they are in different worksheets. Therefore, the headings that Excel uses have to match between the different worksheets or ranges of data in the consolidation.

PIVOT TABLES AND CHARTS

A pivot table is an interactive worksheet table that quickly summarises large amounts of data using the format and calculation methods you choose. It is called a pivot table because you can rotate its row and column headings around the core data area to give you different views of the source data. As source data changes, you can update a pivot table. Because it resides on a worksheet, you can integrate a pivot table into a larger worksheet model using standard formulas. You can also create a PivotChart report to view the data graphically.

CREATE CUSTOM VIEWS

If you need to display or print the same worksheet in a number of different ways, rather than keep having to modify the display settings, collapsing and expanding the outline, changing the magnification, amending the print setup, etc, you can create different views for each one. Custom views are stored within the workbook. By using one of the views you create, Excel will change the worksheet display and print settings stored in the view automatically for you. You are also able to use views in conjunction with printing reports. These allow you to print the views you specify one after the other, rather than having to send each one to print individually. You can also add your custom view to a customised toolbar for easy access.

WHAT-IF ANALYSIS

What-if Analysis
Use the What-if tools when you:

• Want to show the effect that changing figures may have on your budget? Use Scenario Manager.
• Know the final figure, but what figures do you need to change to get there? Use Goal Seeker.
• Need to change both input and output figures to achieve a result? Use Solver.
• You know how much you want to borrow, but how much will you pay back for differing interest rates or loan periods? Use the One and Two Input Tables feature.