A 1 day Introduction Course, aimed to give users the confidence and knowledge to create and use Excel spreadsheets. By the end of the day, users will be able to create, amend and manipulate their own spreadsheets. Course Basics Introduces the user to the layout of Excel, identifying commonly used features and functions. • Menu bar • Ribbon • Status bar Spreadsheet Management Build confidence by performing simple tasks whilst navigating through Excel. • Creating new documents • Saving spreadsheets • Closing Excel Data Management Sorting and Filtering your data to get more accurate statistics from your spreadhseet. • Sorting Data • Subtotalling Data • Using Filters • Freezing Panes Formatting Excel Changing the look and feel of spreadsheets. • Selecting cells, columns and rows • Displaying data as currency (£) • Displaying figures as percentages (%) • Changing cell, text and border colour Formulas and Functions Creating and using simple formulae to return totals, averages, largest and smallest figures from a range of data. • Understanding simple formulas • Using AutoSum • Understanding Absolute Cell References • Using AutoFill tools Formula Auditing Understanding and fixing complex spreadsheets that other users may have set up • Trace Precedents • Trace Dependents • Evaluating formulas • Tracing errors • Using the Watch Window Charts How to use your data in various chart types. • Using the Chart Wizard • Select appropriate chart types • Specifying the data source • Customising your charts
A 1 Day course aimed at users that have attended the introduction course or are an experienced user currently creating and using excel within their role. By the end of the day users will be able to use features including sort, filters, PivotTables and Linking spread sheets. Formulas Shows the user how to action the more enhanced commonly used formulas. • VLOOKUP Formula • IF Logical Tests • Nested IF Formulas • SUMIF Formula • COUNTIF Formula • AVERAGEIF Formula Formatting Cells Quick ways to professionalise your cells formatting and appearance. • Conditional Formatting • Format Painter Tool • Data Validation Working with Data Effective ways of working with sets of Data in Excel. • Using Named Ranges • Formatting Data using Tables • Creating Dynamic Expanding Ranges Excel with other programs Shows the functions of other programs through excel. • Linking multiple excel workbooks • Embedding word documents • Embedding PDF files Protection Restrict the formatting and editing of your spread sheets by users. • Read Only • Restricted editing options • Password protection • Splitting Windows Pivot Tables Creating, editing and understanding the Pivot Table tool in Excel. • Understanding Pivot Tables • Creating Pivot Tables • Modifying data and refresh data • Creating Pivot Charts
A 1 Day Advanced Course, aimed at users that have attended the previous stages or experienced users. This course aims to extend the users knowledge with more tools and commands. By the end of the course, users will be able use features including Goal Seek, Data Tables, Scenario Manager and Macros. Refresh on Formulas Quick refresh on commonly used formulas in Excel. • VLOOKUP formula • IF Logical Tests • SUMIF formula • COUNTIF formula Consolidation How to consolidate your excel data from multiple locations. • Bringing together data from multiple sources • Viewing multiple worksheets • Viewing multiple workbooks Text Manipulation Analysing data from different sources. • Use basic text formulas • Format data using Text to Columns • Connect to an extrernal Data Source using OBDC Connectors What if Analysis Forecasting return figures for business. • Goal Seek • Data Tables • Scenario Manager Forms How to use the form controls to make filling in spreadsheets quicker and easier for your users. • Command Buttons • Check Boxes • Scroll Boxes • Dropdown lists • Data Validation Macros Introduces the user to the basics of macros. • Creating macros • Editing macros • Running Macros • Macro Security • Assign a macro to a command button Customising Excel • Customising excels default settings Modify existing toolbars • Create custom toolbars • Create and edit menu bars
A 2 Day VBA Programming Course (complex macros) aimed at Advanced Excel users. This course aims to give users an introduction to Visual Basic Programming within the Excel application. By the end of the course, users will be able to create their automated procedures. Users will also have a greater understanding of Visual Basic, unlocking the door to the most popular used programming language throughout the world. Visual Basic environment introduction Visual Basic controls – events, properties, text, methods, Forms, Focus Visual Basic definitions – general, event, function, sub procedures and modules The basic language – syntax, instructions, conditions and loops Using variables Text handling – property, functions, strings and variants Creating forms Auto-Fill multiple spreadsheet from user-defined prompts Customizing toolbar menus Creating menus Auto start macros Choices – option buttons, scroll bars, check boxes, list boxes, combo boxes Displaying and printing - message boxes Dialog Boxes – creating a dialog box, using a dialog box and dialog control Error handling Debugging Opening and editing spreadsheets automatically
Thanks , your score has been emailed to you. If there is anything we can help with in the meantime, please call 01709 352307 or email: info@ajtraining.net.
Based on your answers, the most appropriate course for you would be:-
Sign up and we will email you bite sized content for upto 12 weeks after the course.
Extended Learning is optional and charged @ £25.00 plus VAT per delegate per course.
Enquire Now
Price Estimate
Book Now
Sign Up
AJ Training standard rate is £575.00 + VAT. *
* Rate excludes travel expenses, for an approximate on site price please add your details below.
All bookings will be invoiced and payment is due 10 Days before the course date. Following your booking you will receive an email confirming the details including course start times and location, we will attach a course overview, pre course questionaire, directions and an invoice. Should you require assistance or wish to book multiple courses, please call 01709 352307 or email info@ajtraining.net, we will be happy to help. All bookings will be charged at the advertised rates.
All bookings will be invoiced and payment is due 10 Days before the course date. Following your booking you will receive an email confirming the details including course start times and location, we will attach a course overview, pre course questionaire, directions and an invoice. Should you require assistance or wish to book multiple courses please call 01709 352307 or email info@ajtraining.net and we will be happy to help. All bookings will be charged at the advertised rates. The prices shown are per delegate, per day and exclude VAT.
There are currently no scheduled courses. Please fill out the details below and we would be happy to scheduled a course around your availability. Should you require assistance or wish to book multiple courses, please call 01709 352307 or email info@ajtraining.net, we will be happy to help. All bookings will be charged at the advertised rates. The prices shown are per delegate, per day and exclude VAT.
Please complete the form below to enquire about one company / bespoke courses. All bookings will be charged at the advertised rates. The prices shown are per day and exclude VAT.
Our experienced training team have designed each package from the ground up, to ensure it delivers on content and is simple to follow. We always include both video and exercises to help you along the way.
Each of our paid courses comes with our full support, if you get stuck, want to check your understanding or require any additional information, this is all part of the service and our team are here to help!
eLearning package per user @ £39.99 Plus VAT 12 months online access
Although eLearning packages offer a great stand alone product we also offer anyone who attends a standard course the option of adding the complementing eLearning package for only £25.00. For more information or multiuser access please : Call : 01709 352307 Visit : Contact us page Email : info@ajtraining.net
AJ Respond to all online bookings within 3 working hours, we will email you back to confirm prices, availability and content of the requested course. Once confirmed we will forward joining instructions along with an invoice payable 10 days before the course date.
£150.00 per delegate per day
£225.00 per delegate per day
Please complete the form below. If you have a date in mind please let us know by completing the "request date". We will be in touch shortly to discuss your training needs and proposed date for the scheduled course. Should you require assistance or wish to book multiple courses, please call 01709 352307 or email info@ajtraining.net, we will be happy to help. All bookings will be charged at the advertised rates. Prices quoted are per deletgate, per day.
Thank you for your . If there is anything we can help with in the meantime, please call 01709 352307 or email: info@ajtraining.net.
Our course schedule covers the standard training for the coming months, if the dates or course you require is not listed then please call 01709 352307 or email info@ajtraining.net and we will happily schedule additional courses.
Matt was great and made the training interesting
Fun and easy to follow and learn
A good refresher for things I haven't used for a long time and a good insight to how I can make tasks within my role more efficient
Extremely useful, great content with clear practical application. Tom was brilliant.
A very good and useful course that I hope to be able to put into practice at work.
Very clear delivery , exceptionally well delivered considering it was virtual/remote.
Fun and enjoyable learning.
the exercises built nicely and worked well together
Very informative and clear
informative and well given
If you want to find the middle value of a range of figures, then use the MEDIAN function in Excel. Type =MEDIAN( and then insert the range of cells you want to find the Median of. When you hit return it will provide you with the median.
If you want numbers in excel to round up or down to a specified number of decimal places, then use the ROUND function in Excel. The Syntax for the ROUND function is =ROUND(num, num digits).
Num – the cell that you want to round
Num digits – the number of decimal places you want to round to, for example 2 would round to two decimal places
In Excel, you can usually see If a cell is empty. However, sometimes the cell might look empty but in fact it may have a space in it and this can cause problems. To check if your cells are empty, try the ISBLANK formula. Type in the function and the cell reference of the cell you want to check.
If Excel returns the answer TRUE, the cell is empty. If Excel returns FALSE then there is Something in that cell.
If you want to insert the same data into multiple cells, then you can do so using the Flash Fill function. To use Flash Fill, select all the cells that you want to input data into. Once you have selected the cells, type in the required data. Finally, press CTRL + Enter. This will now populate all the cells.
If you want to check that multiple conditions in your spreadsheet are TRUE, then we can use the AND formula.
For example, if you wanted to check if a number is greater than 10 but less than 50 we could use an AND formula. The formula in this example would be =AND(A5>10,A5<50)
Another example that you could use the AND formula is to compare two cells to check that they are the same. The formula for this example would be =AND(A5=10,B5=10)
To put data into a PivotTable, highlight your data and select the Insert tab. On the Insert tab select PivotTable, this will open the Create PivotTable window. Make sure the range of cells it is showing is correct. Next select where you want the PivotTable to be placed. You can either select it to be on a New Worksheet or an Existing Worksheet. Once you have chosen an option, click OK and Excel will now have created a PivotTable for you.
If you want to generate a random number in Excel, you have a couple of options. The first is using the RAND function. Just type =RAND() into Excel and it will give you a random number between 0 and 1.
If you want a random whole number between a specified range, then use the formula RANDBETWEEN. So for example, if I want to generate a random whole number between 1 and 20, my formula would be =RANDBETWEEN(1,20).
If you want to find the largest number in a range of cells, then use the MAX function. To create this formula type =MAX(
After the open bracket input the range of cells that you want to find the highest value for. To finish close your bracket and press enter.
We can use Calculated Items to merge multiple row items in a PivotTable. For example, you would use Calculated Items if you had data for North, South, East and West Yorkshire and you wanted to merge the data into one Item called Yorkshire.
To create a Calculated Item, select a row item and then go to the Analyse tab. On the ribbon click on Fields, Items, & Sets and then choose Calculated Item.
In the Name field, type in the name of your new item
In the Formula field, type the formula to sum the multiple fields together.
For example, = ‘East Riding of Yorkshire’ + ‘North Yorkshire’ + ‘South Yorkshire’ + ‘West Yorkshire’
A quick way to insert a Field Item is to double click on the field you want to insert from the Items box.
Finally click ADD and OK and this will apply your new Calculated Item
An Error Alert message is a pop up that appears if the user is trying to input data into a cell that isn’t allowed to go into that cell.
To create an Error Alert go to the Data tab and choose Data Validation. Next click on the Error Alert tab. On this tab you will have a dropdown with three options, Stop, Warning and Information.
Stop will not allow wrong data to go into a cell
Warning will warn the user that the data they are trying to input is not allowed to go in that cell but will still give them the option of inputting that data.
Information provides the user more information of what data needs to go in that cell
Once you have chosen one of the above options type a Title and Message into the relevant boxes. Finally click OK.
If the Field List doesn’t appear on your screen when you click on your PivotTable, then you can apply it to your screen. Click on your PivotTable and go to the Analyse tab under PivotTable Tools. On the Ribbon select the Field List tool.