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.
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.
the training was well presented and mike was very helpful and informative, brilliant examples given throughout the course.
Very Interesting and Informative
A good course and well managed by the trainer using virtual training. Perhaps the macros topic would need more practical activities.
Exceeded expectations. Felt very comfortable. But a fraction too long
informative and was good to recap at beginning. Would recommend this to friends and colleagues
Very good gave me a much better understanding of the material
Eye opening to the extent excel can do
I am grateful that I had the opportunity to be able to attend on the course, it was really helpful.
Clear, concise, insightful
Very clear and concise
Subtotals are applied to data using summary functions such as SUM or AVERAGE. To apply subtotals firstly select all your data. Next, go to the Data tab and select Subtotal and this will open the Subtotal window.
At Each Change In – The column that you want to Subtotal
Use Function – The function you want to use to calculate the Subtotal
Add Subtotal To – The column that contains the values you want calculate
Once you have completed the above options click OK. Subtotals will now be applied to your data.
An IF formula is where we give Excel a statement and it will return an answer based on that statement. For example, if expenditure is greater than budget then return overspent, otherwise return under budget.
The Syntax for an IF formula is IF(Logical_Test, Value_if_True, Value_if_False)
The Logical Test is the statement that Excel uses to be able to return the right answer. In the Logical Test we must use Conditional Logic symbols which are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), <> (not equal to).
Value if True is the answer you want Excel to return if the Logical Test is met.
Value if False is the answer you want Excel to return if the Logical Test isn’t met.
For your Value if True and Value if False you can use text (which must be in speech marks), cell references, numbers and formulas.
You can use slicers to filter data that is formatted in a table quickly and easily. To insert a slicer into your Excel spreadsheet, select your table and go to the Insert tab. In the Filters group select Insert Slicer and choose the fields from the list you want a slicer to apply too.
You will now have a slicer in your spreadsheet. To use it, just select the option from the list that you want to view. For example, above I have chosen to see the food orders that Terry Walton dealt with.
If you’re referencing a whole column for example B:B and then want to drag the formula across it will change to C:C. If you want the formula to stay refenced to B:B when you drag the formula across then we need to put dollar signs before both letters. For example $B:$B.
If you don’t like the colour schemes that Excel provides then you can create your own colour scheme.
To create your own colour scheme, go to the Page Layout tab and select Colors. At the bottom of the dropdown select Customise Colors. This will then open the Create New Theme Colors window. Finally go and choose different colour for your Accents and Hyperlinks.
To finish give your new colour scheme a name by typing it into the Name box and then click Save.
If you want to show duplicates clearly, how about using the Conditional Formatting tool to show the duplicates with colour.
All you need to do is select Duplicate Values from the Conditional Formatting tool and choose a colour. The duplicates will now be showing in colour.
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.
An Input Message informs the user what data needs to go into the selected cell. To create an Input Message, go to the Data tab and select Data Validation. This will then open the Data Validation window. Next go to the Input Message. In the Title box type the title for Message and in the Message box type the message you want the user to read. Finally click OK.
To create a PivotChart, highlight your PivotTable and select the Analyse tab. On the Analyse tab click on PivotChart and then choose the chart type that you want to use. This will then place your chart into your spreadsheet.
If you like shortcuts, highlight your PivotTable and press F11 and this will create a PivotChart on a new sheet. If you want to create a PivotChart on the same sheet as your table then again highlight your PivotTable and press ALT + F1. This will now create a chart on the same sheet as your PivotTable.
If you want to display Subtotals on your PivotTable then select your PivotTable and go up to the PivotTable Tools tab and select Design. On the Ribbon click on Subtotals and choose where you want the Subtotals to be displayed.
If you want to be able to count items based on multiple criteria, then we can use the COUNTIFS formula.
The Syntax for this formula is:
Criteria Range 1, Criteria Range 2... – the data ranges that you will find the criteria’s
Criteria 1, Criteria 2... - the items that you want count in the criteria ranges