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:-
Implementing skills and practicing are key to any learning, we recognise this and encourage users to engage their new-found skills throughout the course.
After the course we continue to engage users with additional content aimed at providing a more complete training experience and improving skills retention.
We match the content exactly to the skills covered on the course to ensure your training investment is maximised.
Post Course Engagement consists of:
Post Course Engagement is optional and charged @ £25.00 per delegate per course.
Enquire Now
Price Estimate
Book Now
Sign Up
AJ Training standard rate is £525.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 £10.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 1 working hour, 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.
£135.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.
Really enjoyed the course and had time to ask specifics that will help with the tasks we need to undertake. Great course very useful, excellent day and well delivered.
Fantastic course very knowledgeable trainer, very well presented and tailored to our specific requirements and working environment – thanks!
I have been on many courses and I have to say I have never been on a course like the one today. It was informative and hands on and I learnt a great deal. Your offices are in a perfect location, well kept and the course content far exceeded my expectations.
The training was very well received yesterday and Daniel did a great job. The participants are going to use what they have learnt over the next month and then we hope to go on to Intermediate level. Would we be able to request that its Daniel that returns as everyone felt he was very easy to understand and taught it well?
The course was great. Could you please pass on our thanks to Stuart who is a brilliant trainer and very entertaining! We are all hoping to attend the next level, thanks again.
For myself I found it really good. The course was taken at a reasonable pace but each module was thoroughly explained with an exercise at the end to put the learning into context. As a basic user of excel I gained a lot of valuable knowledge and would recommend it.
Course was very useful. The general content was very useful and there was time left to address specific applications for our needs as a group and individuals.
I just wanted to thank you for yesterday, the course was excellent and really helped me. It covered everything I wanted to know and I've been singing your praises all morning! Once again thanks
Thank you so much for the training – it was excellent - you put the information over very well, and just at the correct pace. I’m laughing at the poor state of the docs I did, but they’ll be very useful reminders of everything. The handout on Shortcut Keys is what I’ve been waiting years for and the course notes will be a great help.
I enjoyed meeting yourself and Alison and found the course really interesting, I also enjoyed your training style which suited me down the ground. If I get stuck I’ll send out a ‘help mail’.
Please could you pass on my compliments to Terry, who delivered the course today, as he did so with confidence and humour! He was very helpful and seems like a great personality to have on your team! I very much look forward to attending the other courses that have been booked via yourselves.
If you want to find the highest value in a data set based on one or multiple criteria, then use the MAXIFS function. For example, if you wanted to find Dave’s highest sales figure for a product that has a product ID of 1.1 then I would use the MAXIFS function.
The syntax for the MAXIFS formula is =MAXIFS(max range, criteria range 1, criteria 1, criteria range 2, criteria 2,…)
Max range – the range of cells you want to find the highest value in
Criteria range 1 – the range of cells that you want to find the criteria in
Criteria 1 – the criteria or condition that you want to find the highest value for
Criteria range 2 – the second range of cells that you want to find the second criteria in (Optional)
Criteria 2 – the second criteria or condition that you want to find the highest value for (Optional)
If you want to work with macros or add form controls to your spreadsheet then you will need access to the Developer tab. The Developer tab doesn’t appear as a default in Excel, so you have to add it to your tabs.
To add the Developer tab, go to the File tab and select Options. From the Excel Options window select Customise Ribbon and tick the Developer box on the right. Finally click OK and you will notice the developer tab at the end of your tabs.
If you want to restructure your data so the headers are along the top row rather than the left column or vice versa then why not try the transpose paste option.
To do this, select your data that you want transpose and copy it. Next, click on the cell you want the data to be pasted and then right click and choose the Transpose option under Paste Options.
If you want to replace a section of text, then give the REPLACE function a go. This function allows you to replace a specified amount of characters with new text.
The syntax for the REPLACE function is =REPLACE(old text, start num, num chars, new text)
Old text – the text that you want to replace
Start num – the first character that you want to replace
Num chars – the amount of characters you want to replace
New text – The new text you want to replace the old text with
If you need a formula that will return if a number is odd, then why not give the ISODD function ago. Type in the function =ISODD( and then select the cell you want to reference. When you hit enter it will either return a TRUE or FALSE answer. TRUE means the number is odd and false means that it’s an even number.
If you want to work out what the date is in so many working days, then why not try the WORKDAY function.
The WORKDAY function allows you to count so many working days into the future from a specified date. To use it, type the function =WORKDAY(. Then use the Today function for your start date and a number of working days you want to count from that start date.
It will then give you a number as your answer which you will then need to format into a date.
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 check if a value is text, then use the ISTEXT function. To apply it, type =ISTEXT( and then reference the cell you want to check. When you hit return you will either get a TRUE or FALSE answer. If you get TRUE returned, it means the cell you have referenced is a text value. If FALSE is returned, then you have a numerical value in the cell you referenced, for example, a number, date or time. A blank cell will also return FALSE.
If you want to find which number appears most often in a range of cells, then use the MODE function. In a cell type =MODE( and then insert the range of cells you want to find the mode of. When you hit return it will provide you with the mode.
If you don’t want to spend time going through data to find unique values, how about using the Conditional Formatting tool to show the unique values with colour. It’s rather simple to apply, select the Conditional Formatting tool and select duplicate values from the Highlight Cell Rules menu.
Once there, change Duplicate to Unique and select a colour. Your unique values should now be colour.
If you want to search for a string of text within a larger string of text, then use the FIND function. This function will return the character number of where the string of text is located. For example, if you wanted to find the letter O in laptop, the FIND function would return 5 as O is the fifth character along.
The syntax for this formula is =FIND(Find Text, Within Text, [Start Number])
Find Text – The text you want Excel to find
Within Text - the string of text you want to find something in
Start Number – the character that you want Excel to start searching from (optional)