Database Functions in Microsoft Excel

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 https://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.

Visio brainstorming diagram

This video explains how to create a Visio brainstorming diagram and how to create a diagram using charts and marketing diagrams. The tutorial also explains how to use the developer tab to protect shapes and shape elements. The final section covers how to combine and fragment shapes and add the features to the quick access toolbar. Online courses at http://www.itseasytraining.co.uk

Details about Microsoft Visio classroom based courses can be found at https://www.itseasy.co.uk/courses/visio-training/ all courses are delivered either at our site in Gateshead or your site if required. We can provide ten laptops and all the software versions anywhere in the UK. Prices can be found at out prices section and can be tailored for multiple groups. If you don’t ask you don’t get.

Visio Brainstorming

This video explains how to create a Visio brainstorming diagram and how to add topics which will automatically update the legend when added.

Charts and Graphs

This part of the video explains how to create each element of a bar chart, a pie chart and how they work. Adding the marketing shapes stencil also shows how diagrams are edited and modified.

Protection

The last section of the video covers why some shapes are protected and where the protection can be found. Using the combine feature with fragment utilises the add to the quick access toolbar process.

Visio linked data

This video explains how to create a flowchart using the Visio linked data wizard, pulling the data from Microsoft Excel into a Visio flowchart diagram. The video also explains how to import data from Microsoft Project in to a Visio timeline diagram. The tutorial covers how to edit data graphics in Microsoft Visio and how to update linked data using the refresh option.

Contact

The course details for the topics covered in this video can be found at https://www.itseasy.co.uk/courses/visio-training/ online courses can be found at http://www.itseasytraining.co.uk

Importing data

The process to import data from other programs is quite simple in Microsoft Visio, you can use the import wizard or select the link option on the timeline tab for Microsoft Project for example. The video uses the Timeline diagram and selects data from a Microsoft project file. The resulting Timeline is a bit cluttered and extra effort is required to sort out the labelling and positioning of the tasks however, the end product looks great.

Visio Linked Data

If you have used the Visio linked data option, the process as shown in the video, proceed as follows: Select the shape type you require and then select the linked field dragging it into position on the diagram. You can update data in the source files which will be refreshed when selected in the Visio diagram. You can add additional shapes and connections to enhance your diagram that don’t have to be linked to a source file.

Data Graphics

The video explains how to edit linked data in the data graphics feature. The graphics can be moved deleted or added to through the wizard. If you remove the data graphic you will still be able to type in the shape boxes and format them as normal. The linked data will appear in the Shape Data window if it is active.

Visio Reports

This video explains how to run and create reports in Microsoft Visio and send them to Microsoft Excel. The tutorial demonstrates how to create shape data and add the new shape data field to a shape report. Once the new shape data is added, the field appears in the wizard ready for selection, once all the required fields are selected the report is generated by following the wizard through to the end. The video also covers how to add subtotals to your report, to total, either number or currency fields. Finally, in the last section, how to edit the Excel output is explained.

Overview

The course details for Microsoft Visio are at https://www.itseasy.co.uk/courses/visio-training/ online courses are available at http://www.itseasytraining.co.uk

Visio Reports

All Visio shapes have shape data fields which are either preset or can be added by the user. Once the fields are used to store data the data can only be sent one shape at a time. This can be slightly irritating. To solve this issue Microsoft Visio provide a report feature.

Shape Reports

There are several preset report but it is very easy to create your own report. Simply add all the filed required, including new shape data fields and then follow the wizard through. There are several report options but i tend to send my report in to an Excel file, which can then be formatted in Excel.

How to create a Floor Plan using Microsoft Visio

This video explains how you can use Microsoft Visio to create a floor plan to convert your garage or other areas of your house. The tutorial covers how to set outside walls and how to create windows and set the size, how to add doors and flip them in to the correct position. The video also explains how to create a layer and lock the contents of that layer. The search feature is explained and adding new shapes to your own stencil is also covered. Training at www.itseasy.co.uk Online courses at www.itseasytraining.co.uk

The details shown in the video are covered on the Visio overview course, details of the topics covered on the course can be found at https://www.itseasy.co.uk/courses/visio-training/ online courses can be found at http://www.itseasytraining.co.uk

Floor Plan

This video explains how to use Microsoft Visio to plan a conversion in your house. The video shows how I used Microsoft Visio to plan for my own garage conversion and some of the mistakes I made. The number one error was not measuring the furniture and walls correctly. Because of my mistakes I was left with a wall with no door, and a section of the house with no lights.

The personal stencil

The video demonstrates the use of a personal stencil. I always recommend to my students that they should create a personal stencil that they can add shapes to, which in the end will speed up development of diagrams.

The stencils will be by diagram groups, for example a floor plan shape may not work in an organisational diagram, the scale may be too small to fit. I try to create a stencil for each diagram type I use. You can drag and drop shapes on to your stencil and then double click the icon to rename it. You must save the stencil and note where it is saved.

Visio Gantt charts

This video explains how to create Visio Gantt charts using the schedule diagram template and how to update tasks, link tasks and format tasks in Visio. The tutorial also explains how to import Microsoft Project files in to a Visio Gantt chart

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

Visio Gantt charts

Creating a Visio Gantt chart is a simple process but in my view only of use to present summary data. To use Microsoft Visio to actually run and manage a project would be very difficult due to the lack of Project tools available within the template. Having said that the features available are very good at displaying your Project plan in a great visual way.

Importing from Microsoft Project

Using the Import wizard allows you to bring data from a Microsoft Project file that you have already created. During the import process you can select whether you want all the task details or just summary tasks. The end product is a very visual Gantt chart which you can format from the many preset options on the ribbon.

Linking and updating tasks

When you use Microsoft Visio to create a Gantt chart there doesn’t seem to be many tools to help you edit the task list. The ribbon has tools that will help you link tasks and indent task and other basic features but you will have to use shape data to get in to some of the other Project management tools available. You can list resources or add % complete information, you can also create several custom fields within shape data in addition to any new shape data field you might want to create for yourself. If you amend the % complete field the result is shown on the task bar on the Gantt chart.

Synchronised shapes in Microsoft Visio

This video explains how to create Organisational charts and use the synchronised shapes feature in Microsoft Visio. The video also explains how to add shape data and use the team frame feature.


Online courses at http://www.itseasytraining.co.uk

Overview

How to use organisational charts and synchronised shapes in Microsoft Visio. The course details are at https://www.itseasy.co.uk/courses/visio-training/ courses are available in a classroom environment or online at http://www.itseasytraining.co.uk

Organisational charts

Organisational charts in Microsoft Visio are very easy to create and edit. The basic structure is the same whichever level you are trying to create, simply drop a subordinate shape on top off the management level and Microsoft Visio snaps it in to the correct position.

Users can quickly add data to each shape, ether directly in to the shape or via the shape data feature. Shape data can be adjusted to suit by using the define shape data feature. Additional shape data fields provide a great data storage feature which can be used to generate reports.

Synchronised shapes in Microsoft Visio

It is very easy for organisational diagrams to grow quickly and become very unmanageable. Using the synchronised copy feature in Microsoft Visio is one way to alleviate size and unmanageable clutter issues. The synchronised copy only synchronises data, not additional shapes. Basically, once you have synchronised shapes, any additional shapes added will not appear on the synchronised copy or the main copy if they are added after the first synchronisation. To add extra shapes you will need to re-run the synchronisation.

Groups of data and connections are easily moved around by using the container option. This feature can be added via the right mouse click or the ribbon. Once shapes are in a container it is easier to move them around and apply formats through the container tab on the ribbon.

Cross Functional flowcharts in Microsoft Visio

This video explains how to create a cross functional flowcharts in Microsoft Visio. The tutorial demonstrates how to add basic shapes from the stencil and connect the shapes manually to avoid swim lanes being dragged all over the place. The video covers how to use shape graphics and how to edit shape graphics. The shape graphic is triggered by data in the shape data box. The final section of the video explains how to define shape data.

Overview

This video explains how to use a Cross Functional flowcharts in Microsoft Visio. The tutorial demonstrates how best to add shapes from the Visio stencil without over sizing the swim lanes.

Data Graphics

The video explains how to use data graphics to gave a visual indication within the flowchart shapes. There are several options that may be used, icon sets and text for example.

The process is quite simple, select a shape data field or add a new one, then create the shape graphic based on the shape.

Swim Lanes

The swim lanes on the default cross functional flowchart can grow to accommodate shapes if they cross over swim lanes. The shapes that are added manually do not seems to effect the swim lanes in the same way, if connectors are added after the shape is added and not with the automated shape builder. I find it best to position all the shapes across the swim lanes and then go back to add the connectors. It may be a bit longer but i have found that it is quicker in the long run.

Online courses are available at http://www.itseasytraining.co.uk classroom based courses are available at https://www.itseasy.co.uk/courses/visio-training/ Classroom based courses can be delivered onsite with our laptops or at our Gateshead site at Metropolitan House,  Longrigg,  Road,  Swalwell,  Gateshead,  NE16 3AS

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 https://www.itseasy.co.uk/courses/excel-training/

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

Microsoft Visio basics

This video explains Microsoft Visio basics. The tutorial explains how to add shapes from the stencil to your diagram and how to use the following tools: The connector tool, the text tool, the text block tool, the connection point tool and the pointer tool. The video also explains how to format shapes and group shapes. You will learn how to use tabs and paragraph format options.

Online courses at http://www.itseasytraining.co.uk

Overview

I find one of the most frustrating issues with Microsoft Visio is the connector tool and the pointer tool. Basically what happens when you use any of the tools in the tools block on the ribbon, they do not automatically reset to the pointer tool. The consequences of this are multiple text boxes and or orphan connector lines.

This video explains how to use the connector tools as mentioned above but it also demonstrates connecting different types of shapes and shows what happens when you have no connection points. Visio basics is a must if you want to understand how Microsoft Visio works. It will help you as you get in to the other diagrams and start to develop your skills. You will understand why certain shapes behave in a certain way.

I find that a lot of people add additional text boxes instead of using the shape text features. This can cause confusion if someone else wants to edit the diagram. Keep it simple and use the tools available. Try this, instead of adding additional text boxes use the text block tool to move the text box off the shape, as shown in the video. The course run by IT’s Easy Training is a one day classroom based course either at our Gateshead site or on your site with our laptops.

The course overview can be seen at https://www.itseasy.co.uk/courses/visio-training/