Category Archives: Excel Training

Excel Maps

The following two videos explain how to use the Excel maps feature and the 3D Maps feature powered by Microsoft Bing.

Both these topics are cover on the Excel courses provide either online at http://www.itseasytraining or classroom based onsite or in our Gateshead training room.

Details and course outline for Microsoft Excel training courses can be found by clicking here.

Excel Maps

The first video explains how to create a map based on geographical data. The example uses data from four European country. The tutor mentions how to change the layout, colour and style of the map. The Map feature is part of the Charts group and complements it very well.

3D Maps

When you use the 3D map feature you need an Internet connection for it to work. You also require geographical data so that the tool can plot the map. You create a series of tours, these tour will play along a timeline if you have date related data selected as shown in the video. If you are happy with the end product you have the option to save the tour as a video and then email or share it with colleagues. There are possibilities to create more complex tours by adding extra layers with additional animations.

Microsoft Office Courses

Overview

The Microsoft Office , Project and Visio buttons displayed below link to the course outlines for classroom based courses. If you wish an online course go to http://www.itseasytraining.co.uk

You can assess your knowledge levels by completing the Excel document here Check Skill Level Once you have completed the document email to info@itseasy.co.uk You can book courses via email or by calling 01670 732139.

Prices and location can be found here General information about IT’s Easy Training and delivery methods can be found here

Microsoft Office Courses

Microsoft Office course delivery is broken down in to three parts Introduction, Intermediate and advanced levels. Some courses have only one level but may take more than one day. All courses can be customised, the layouts are a guide only.

Microsoft Project classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft Project helps managers to plan, organise and resource projects of all sizes, with features that help you to save time, money, stress and avoid missing key deadlines.
Microsoft Access classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft Access is a powerful database package, yet many people find it difficult to use fully. Our courses will equip you with the skills and experience you need to master Access databases.
Microsoft Visio classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft Visio is a powerful diagrammatic tool that is great for flowcharts, process diagrams, organisational charts and much more
Microsoft Outlook classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft Outlook provides an information hub for contacts, calendars, tasks and emails
Microsoft Excel classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Whether you are new to Microsoft Excel or need to develop existing skills, our popular Excel courses will help you to develop your skills and feel more confident using Excel
Microsoft Word classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Many people struggle using Microsoft Word. On our courses you’ll learn basics such as typing and layout skills through to advanced features, making you more confident and productive
Microsoft PowerPoint classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft PowerPoint in a visual presentation tool that provides exciting and comprehensive delivery techniques to aid with predestination style and content
Microsoft Publisher classroom based courses in Newcastle, Gateshead and UK wide, onsite if required.
Microsoft Publisher provide a template based set of documents, leaflets, brochures and much more

Lookup Images

This video explains how to use the Indirect function and the DGet function to lookup images and data in an Excel sheet. The tutorial covers how to use named cells and ranges, also how to use data validation lists.

Training courses are available at http://www.itseasy.co.uk/courses/excel-training/ courses can be delivery onsite or at our site in Gateshead. Online courses are available at http://www.itseasytraining.co.uk We have laptop which are available to use at your site and convenience. Call or email steve@itseasy.co.uk Business Mobile 07966503354

Lookup Images

The process in this video starts off explaining named cells and ranges, each photo sits inside a named cell. The tutor explains how to create a data validation list that displays the names of the photos. The indirect function is added in the name manager to reference the data validation box.

Indirect

The Indirect function is added to the name manager, by selecting new and creating a name. The Indirect function returns data from a referenced cell, in this case the image.

Images

Any of the images in the data set can be copied and placed in the relevant location on the lookup sheet. With the image selected the tutor explains that the formula must be typed using the Indirect name, (Flag) in the video example.

DGet

The DGet function is used to bring the remaining data across using the Data Validation list as its trigger. The DGet function works in a similar way to a Lookup working with a database of information.

I think that the best uses of these functions is in a catalogue of stock or parts. The parts or stock could have a photo which would be returned as and when required along with all the other data normally associated with lookups. Check out our YouTube channel at http://www.itseasytraining1st for more videos like this. Feel free to ask questions or ask for more videos.

Database Functions

This video explains how to use Database Functions in Microsoft Excel.
The tutorial covers the DSum, DMax, DMin, DAverage and the DGet functions. Each example uses named ranges and the tutor explains how to create a two way lookup using the DGet function.
Some of the examples use the date validation list option.

Classroom based training courses are available at http://www.itseasy.co.uk/courses/excel-training/ Online training courses are available at http://www.itseasytraining.co.uk

Database Functions

To get the best out of Database functions you need to create a database of records and then a selection area where the column headings match the database headings.

Set the functions to look at the selection area to calculate the required data. Basically the Database functions use the set criteria to match your selection from a database. It is easy to set up multiple criteria and may be easier to use than the very popular Vlookup or Hlookup. The functions are smaller than comparable SumIFs and CountIFs therefore use less memory in large spreadsheets.

DGet function

The DGet function enables users to create a two way lookup similar to the Match and Index functions. The DGet function required a Database and a field followed by a selection area. If you use named ranges and cells the formula can be copied across without worrying about using dollar signs to lock cells and ranges.

How to use PowerPivot in Microsoft Excel

PowerPivot in Microsoft Excel

How to use PowerPivot in Microsoft Excel. This tutorial explains how to create a relationship between two Excel tables, which have been formatted as tables, using the PowerPivot tab. The video explains how to use the Manage options in PowerPivot and how to link the tables using the diagram view.

Check out online courses at http://www.itseasytraining.co.uk

Overview

This section explains how to add relationships between tables using PowerPivot in Microsoft Excel.

Two tables that will be linked
Two table to start with

In the above example there are two small tables of data which I am going to join using the PowerPivot window. When I add the two tables to the data model, they will formatted to Tables. You can rename the tables on the sheet tabs, or use named ranges.

Saving a table to the data model
Save each table to the data model

First step is to add each table to the data model.

check the my table has header option
Check the my table has header option

Make sure that you check the My Table has Headers option.  The following screen is displayed, cancel this screen to add the other table.

Manage table

When you add the second table, you will see a second tab appearing in the PowerPivot window.  You can name the tabs if required.

You may notice that you can import data from this window as shown before. We are now going to create a Pivot table based on these two tables. Select the Pivot table button and select where you want the Pivot Table to sit. You can also select the Pivot table option from the Manage window. The Manage windows allows you to select quite a few different Pivot table and chart options.

pivot table range

You will notice that both tables added to the data model now appear in the Pivot field window.

Pivot table window through the PowerPivot tab

I am going to link these two tables via the department field.  I can do this through the normal Pivot table tools tabs, but I find it a lot easier to use the PowerPivot window and the Diagram view option, because it is more visual

Relationship window

Drag the department field from one table to the department field in other table, when you release the connection is made. Once the relationship is created you can use field from both tables as shown below:

final linked relational table

I have department and surname set to row from one table and manager set to column from the other table.  Salary is the summary field.

Excel courses can be found at http://www.itseasy.co.uk/courses/excel-training/

Online courses can be found at http://www.itseasytraining.co.uk

Hot tips and time savers

This tutorial explains how to save time in Excel and therefore save money. The video covers some of the more common key commands and features such as flash fill, inserting columns, selecting data sets, time commands, saving shortcuts and instant charts.


Online courses at www.itseasytraining.co.uk