For information about our training programs, please email Information@SudburyTraining.Com

If you are interested in commenting on or discussing any of the articles in the Sudbury Training Blog, please feel free to do so. In order to post a comment you must register as a site subscriber. To register, click here and then click the Register link. We moderate comments so anything you post may not appear immediately.

In this article, guest blogger Nick Williams extends creating a gauge chart. Nick is an Access tutor based in the UK.

Here you’ll learn to create a slightly more advanced kind of Gauge chart with will actual have a reference gauge and pointer to indicate the desired value. You can refer to the advanced Gauge Chart tab of the embedded Excel file.

Let’s get started.

  1. Let’s assume we want to represent 140 as a pointer in the range of 180 and we have 3 categories in the gauge at intervals of 60 each. For this setup, we will create the data table accordingly
  2. Insert 60 in B2, B3 and B4 for each category in the gauge chart
  3. Add sum of B2:B4 in B5. Add the pointer value in C2(in this case 140). 1 or 2 in C3 which defines pointer width and enter this formula in C4: SUM(B2: B5)-C3-C2
  4. The formula helps in creating the remaining value of the Pie chart

  5. Now Select the range A1:C5 and Inset a Pie chart by navigating to the Charts section of the Insert Ribbon. After inserting the chart, in the Design tab of Chart Tools ribbon select Change Chart Type
  6. Select combo chart from bottom left of the pop up window generated
  7. Select Doughnut chart for Range series and Pie for the Pointer series. Make sure the Secondary Axis check box is ticked for the Range series. Then Click OO to plot the graph

  8. Remove the legend and the title or modify the title as per your requirement
  9. Right click on the chart area and select Format Data Series

  10. This will open a section to Format the Data series on the right
  11. Make the angle of the first slice as 270° of both the Doughnut and Pie Chart. You can switch between the series by clicking the respective graph and set the Doughnut hole size to 60

  12. Now click on the bottom half of the Doughnut chart to modify the format of only that slice (You might have to click twice if the slice isn’t selected). Click on the paint bucket icon available on the right. In the Fill section select no fill
  13. You can select No border in the border section, remove the border from all the other slices
  14. Repeat the same steps for the Pie Chart as well. One additional step for the Pie Chart would be to remove the fill of the initial slice that is the pointer value (the other big slice)
  15. Now change the fill of the remaining 3 slices of the doughnut to indicate different categories. I’m formatting them to Green, Orange & Red for Low, Medium & High respectively
  16. Now the chart should look something like the following:

  17. Again, Select Change Chart Type from the Design tab of the Chart Tools ribbon. Here change the Secondary Axis to the Pie Chart or the Pointer series and click OK. Change the fill of the Slicer of the pointer to desired color (I’ve selected black)
  18. Now, Select the entire chart area and in the fill, select no fill and No line in the border section
  19. Position the graph in so that a cell aligns to the center space and reference that cell to the actual percentage we have plotted (here 135)
  20. Remove Gridlines by unchecking Gridlines from the Show section of View ribbon

  1. Now we have an advance gauge chart created:

Vary the pointer value in the initial table to see the graph change. As mentioned earlier even a formula can be written to determine the position

We welcome back guest blogger Nick Williams. Nick is an Access tutor based in the UK.

In this article, you will learn how to create a gauge chart in Excel.

Let’s assume we would like to represent % value in a gauge chart. I’ve embedded the Excel workbook used for this article. Feel free to refer to it as you read through the article.

Let’s begin by creating a suitable data to plot the chart.

  1. I’m selecting 80% as the percentage to represent (I’m plugging that into cell B2), the same can be changed to any desired value or even a formula can be used to determine the value
  2. Then subtract the value (in this case 80%) from 1 or 100%, use relevant formula to do this in Cell B3
  3. Add 100% in the cell B4. Later you’ll understand why this is necessary

  4. Now Select the range A1:B4 and Inset a Pie chart by navigating to the Charts section of the Insert Ribbon
  5. Select the option for a Doughnut chart, you’ll get chart similar to the one shown below

  6. Remove the legend and the title or modify the title as per your requirement
  7. Right click on the chart area and select Format Data Series

  8. This will open a section to Format the Data series on the right
  9. Make the angle of the first slice as 270° and set the Doughnut hole size to 60

  10. Now click on the bottom half of the Doughnut chart to modify the format of only that slice (You might have to click twice if the slice isn’t selected). Click on the paint bucket icon available on the right. In the Fill section select no fill
  11. You can select No border in the border section, remove the border from all the other slices
  12. Fill the smaller slice with a lighter color to denote the remaining value to achieve 100%
  13. Now the chart should look something like the following:

  14. Now, Select the entire chart area and in the fill, select no fill and No line in the border section
  15. Position the graph in so that a cell aligns to the center space and reference that cell to the actual percentage we have plotted (here 80%)
  16. Remove Gridlines by unchecking Gridlines from the Show section of View ribbon

  1. Now we have a gauge chart created:

In Part II, we will discuss making a more advanced version of gauge charts.

 

Links and references in this page are current as of 22 May 2017. Links are provided as a courtesy by Sudbury Training for information purposes only without warranty, implied or actual, as to the safety or propriety of the linked site. Always use caution when browsing to any internet site.



Newcomers Guide Links

Personal Computing

Working with Windows

Privacy and Security

Web Browsers

On-Line Accounts

Social Networking

General



Personal Computing
        TOP

Personal computer – Wikipedia

Cloud computing – Wikipedia



Working with Windows
        TOP

Move the Taskbar to Different Locations In Windows

How to Customize the Taskbar in Windows 10

Windows 10 – How to Move the Taskbar – YouTube



Privacy and Security
        TOP

Privacy concerns with social networking services – Wikipedia

Privacy concerns – Potential Dangers

Create a strong password

The Little Black Book of Scams – Competition Bureau

Canadian Anti-Fraud Centre / Centre antifraude du Canada

Fraud types

Strong Random Password Generator

Help protect your Microsoft account

Protect yourself against fraud

IT support scams: 4 tips to protect yourself – Co-opme Desjardins

Facebook Scams You Need To Know About: The 9 Most Common Hacks And Attacks | HuffPost

Help To Remove ‘Windows Defender Alert’ Pop-Ups Virus – Remove All Virus

What’s the Privacy Checkup and how can I find it? | Facebook Help Center



Web Browsers
        TOP

Web browser – Wikipedia

Browser Help Pages

Help – Firefox

Help Google Chrome

Help – Microsoft Edge



On-Line Accounts
        TOP

Can I sign in to Skype with my Facebook account?

Create your Google Account



Social Networking
        TOP

List of social networking websites – Wikipedia

List of virtual communities with more than 100 million active users – Wikipedia

Facebook Help Center

Instagram



General
        TOP

Fresh water islands in Canada – Google Search

Fresh water islands in Canada – Bing

Facebook SudburyTraining2017

TOP

 

Contact Sudbury Training

 

Image result for NAVIGATIONNavigating within an Excel workbook is simple enough if the workbook contains one or two worksheets and if you are familiar with working with multiple worksheets. However, if the workbook as more than a few worksheet or the user is not comfortable with a multiple sheet environment, navigation can be a little more involved, even a little intimidating.

In this article we will discuss creating and using hyperlinked drawing objects to create an intuitive menu system for an Excel Workbook.

 

SNAGHTML9f2d87e

image

Scenario

The workbook we will be enhancing is used to manage attendance records for employees at each of five locations. Each location is represented by one worksheet for each of three shifts plus one worksheet for the location’s employee list. Unless you are working on a very wide monitor and/or at very high resolution, seeing the tabs for all sheets at the same time will be unlikely:

image

So going directly from Location A to Location D, for example will require at least one extra step in addition to a mouse-click on the desired sheet tab. In some Excel version two additional steps are needed. First you need to right-click in the sheet navigation area, then select the desired sheets. In some versions of Excel, you will only see 15 sheet names when you right-click the navigation area and may have to select “More Sheets” to be able to access all sheets.

SNAGHTML4ac3ffd

 

image

A custom navigation pane will make the process of navigating from one location to any other location in the workbook simpler and more efficient that the stock method of selecting sheet tabs.

‘Own-use’ vs. External User

While the same general techniques apply whether you are designing a navigation panel in a workbook solely for your own use or for another person who will be working with the workbook on a regular basis, designing the panel for someone else to use requires more thoughtful and detailed planning.

Objectives

The navigation should be simple to use, have an attractive look and feel that is consistent throughout the workbook, be visible at all times, and require a minimum number of mouse clicks for the user to switch from one worksheet to another.

Create a Prototype Menu

The more time you put into designing and perfecting a prototype menu, the easier it will be to apply the finished menu to the rest of the workbook. Start by creating an additional sheet which you will use as a sort of drawing canvas for creating the prototype menu. In the sample workbook, this sheet is named ‘Menu Prototype.’

If you used consistent naming principles for the worksheets when  you created the workbook, you can take advantage of Excel features like copy and paste and object grouping to take some of the tedium involved with building your prototype menu

  • Drawing Objects

Almost any drawing object is suitable for individual menu elements. Simple rectangles arguably make the most efficient use of available space.

  • Effects

Excel drawing objects, especially in the 2007 and later versions, offer a wealth of visual effects to set them off from normal worksheet contents.

  • Hyperlinks

Hyperlinks can be applied to most drawing objects. Waiting to apply them until you have fixed the basic properties of the object makes them a little easier to work with.

  • Properties

By default, drawing objects are moved and re-sized with cell. To ensure the menu doesn’t get automatically moved or re-sized, change the relevant property.

NOTE: Screenshots in this article were developed using Excel 2016. If you are using an earlier version of Excel, what you see an your screen may look a little different from these illustrations

Step-by-step, here’s how to build a prototype menu.

  1. Create a simple drawing object such as a rectangle. Use your mouse to drag the crosshair ( c ) to draw the rectangle shape. You can refine the size of the rectangle to what works best for you.SNAGHTML5a89b04

SNAGHTML5a949b3

After you have drawn the shape, click to select it

image

 

and set the height and width of the object on the Drawing Tools Format Ribbon Tab ( f ).

image

2. With the object selected type a caption that will indicate where the button will take the user when they click it. Use the Home Tab’s alignment buttons to align the caption within the button.

SNAGHTML955626f

3. Duplicate the button. You can use copy and paste but it’s faster to use Ctrl D (duplicate). You will need as one copy of the button for each option you want to offer the user. You will need to modify the caption for each button but it is faster to do this after you have made all duplicates you will need.

TIP: In some versions of Excel, if you position the first duplicate where you want it relative to the original, then continue duplicating, additional duplicates will automatically position themselves relative to the previous duplicate.

4. If necessary, drag the buttons roughly to the positions you would like them to be relative to each other. Use the Align commands on the Drawing Tools/Format Ribbon tab to refine their alignment. Select all of the buttons and use the distribute commands to make the layout more symmetrical.

image

image

5. When you have the buttons laid out to your satisfaction, with all the buttons selected use the Group command to combine them into a new custom object.

6. With the new group selected, use shape effects to give your menu panel and buttons a distinct ‘look and feel.’

SNAGHTML98f8946

 

image

7. With the menu group still selected right-click it and select size and properties. In the size and properties pane (or dialogue, depending on your Excel version), set the size property to “Don’t move or size with cells.”

image

 

8. De-select the group and then select each button in turn to assign its hyperlink.

SNAGHTML9a9572b

Choose “Place in This Doucment ( a )," then select the name of the sheet to which you want this button to lead ( b ). Type the name of the cell you want to activate on the selected sheet ( c ). Then click the ScreenTip button and enter the tip you would like to pop up when the user’s mouse hovers over the button.

image

Pay close attention to all the details when you set up your prototype menu. Once you are satisfied that it looks and performs as you want it to, simple copy and paste it to every worksheet in the workbook.

Apply the Menu to all Worksheets

Positioning the menu panel in a consistent location on every worksheet will give your workbook a more professional appearance. Here is a horizontal menu located at the top left or a worksheet.

image

And here is a vertical menu, also located at the top left of a worksheet.

 

image

You can also remove the hyperlink from the button that represents the sheet on which a menu panel is located and ‘grey-out’ the caption for that button.

image

For horizontal menu panels, set the height of Row A to the height of the menu panel. For vertical menu panels, set the width of Column 1 to the width of the menu panel. If you want to prevent then menu panel from scrolling off, use the freeze panes command to lock the top row or first column of each worksheet.

Advantages

  • Intuitive
  • Makes navigation easier
    • Direct access to specified worksheet
    • Direct access to specific location, if needed

Disadvantages

  • Takes time to plan and create
  • Sacrifices some workspace

A hyperlinked menu navigation panel will simplify moving from sheet to sheet in workbooks that have more than a few worksheets.

Autofill has long been one of my favourite Excel productivity features. It is the fastest way I know to copy cell contents to multiple cells. If you have an existing column of data and want to one or more formulas to adjacent columns, double-clicking the fill handle adds a whole new layer of productivity.

This video illustrates this tip and the small surprise that Excel 2016 introduced that affects this feature. By default, autofill reproduces a series; in other words if the cell that you want to use as the source data for autofill contains a value like, Room 101, autofilling this cell down a column will give new values like Room 102, Room 103, Room 104 ….

That’s all well and good, but what if you wanted to assign a number of people to the same room, Room 101? In previous versions of Excel, immediately after autofilling, Excel displayed the Paste Options button. The surprised that Excel 2016 introduced is that the default does not display Paste Options.

That can prove very frustrating, especially if you autofilling cells across may rows to many columns. Fortunately, the solution is relatively simple: enable the Paste Options button by modifying your Excel Options to Show Paste Options button when content is pasted.

image

Check it out!

Are You a OneNote Fan?

Have a look at this article. It has some valuable tips on note-taking. Although the article is addressed to medical students, anyone who needs to take notes of any kind will find some excellent tips here.

Some keyboard shortcuts are designed to enhance your productivity in specific applications. Here are five that have long been my favourites in Excel.

 

<ctrl>;

Insert the current date. The inserted date will remain the same; it does not update when Excel recalculates formulas.

<shift><ctrl>:

Insert the current time. The inserted time will remain the same; it does not update when Excel recalculates formulas.

<ctrl>HOME

Move the selection to the beginning of the worksheet. Cell A1 becomes the active cell. Note: if freeze panes is in effect, the shortcut behaves a little differently, making the top left cell of the lower right quadrant active. If Row 1 contains the header of a structured table, <ctrl>HOME will select cell A2 rather than A1.

<ctrl>END

Selects the cell at the lower right corner of the portion of the worksheet that has actual contents.

F4

When entering formulas, make a cell reference absolute. Each time you press F4 when you are entering a formula the absolute reference changes from Absolute Cell or Range to Absolute Row/Relative Column to Absolute Column/Relative Row to Relative Cell or Range

Links for the technically inclined:

Announcing the Microsoft Cloud Roadshow

https://microsoftcloudroadshow.com/cities/?Wt.mc_id=dx_MVP400580

This is a free, two day technical training event for IT Professionals and Developers that provides best practices and insight from those who run cloud services across Office 365, Micros oft Azure, and Windows 10.

Developer Interview Series #1 –

The Power of Cross Platform Development with Universal Apps and Xamarin

https://channel9.msdn.com/Blogs/Case-Studies/The-Power-of-Cross-Platform-Development-with-Universal-Apps-and-Xamarin?WT.mc_id=dx_MVP400580

In this interview series, we bring you best practices, anecdotes, and insights from developers who are building creative solutions using Microsoft technologies.

We’re on the road to self-driving business applications

http://blogs.msdn.com/b/stevengu/archive/2015/09/09/we-re-on-the-road-to-self-driving-business-applications.aspx?WT.mc_id=dx_MVP400580

A blog by Steve “Guggs” Guggenheimer on self-driving ERP (Enterprise Resource Planning software)

Memory Compression in Windows 10 RTM

https://channel9.msdn.com/Blogs/Seth-Juarez/Memory-Compression-in-Windows-10-RTM?WT.mc_id=dx_MVP400580

The OS is doing some clever optimizations that allow your processes to trim some of the memory but not necessarily page it out to disk.

Managing hidden apps, beta apps and visibility of in-app purchases in Dev Center

http://blogs.windows.com/buildingapps/2015/09/10/managing-hidden-apps-beta-apps-and-visibility-of-in-app-purchases-in-dev-center/?WT.mc_id=dx_MVP400580

The unified Dev Center introduced several new options to manage the visibility of apps and in-app purchase.

ShortcutTrying to learn keyboard shortcuts can be intimidating; there are just so many. Like most problems, however, you can overcome the problem by breaking it down to bite-sized chunks. So if you haven’t been using keyboard shortcuts, start by learning and using just these five. Chances once you have used them only a few times, they will become second nature. Remember that some keyboard shortcuts require a combination of keys. That means you press and hold the first (second, and third, in some cases) and tap the last. As you will see, frequently the key to use makes logical sense but sometimes it doesn’t.

<ctrl>A Select the entire document
<ctrl>X Cut the selected content or object(s)
<ctrl>C Copy the selected content or object(s)
<ctrl>V Paste what you have cut or copied
<ctrl>Z Undo the last action you did

These particular keyboard shortcuts (and many more, actually) have a consistent Beachmeaning throughout the Windows world. Whether you are working with a Word Document, an Excel Workbook, a PowerPoint Presentation, any other Office document, or Windows (File) Explorer, you use the same keyboard shortcuts.

Why bother learning these or other keyboard shortcuts? It’s all about time. To be sure, the time you save by using a keyboard shortcut once is insignificantly brief. But these shortcuts do things that you do frequently and repeatedly whenever you are working on your computer. Think of those tiny slivers of time as grains of sand on a beach. Save enough of them any you will have time for a beach vacation.

Links for the technically inclined:

Announcing the Biggest VM Sizes Available in the Cloud: New Azure GS-VM Series

http://weblogs.asp.net/scottgu/announcing-the-biggest-vm-sizes-available-in-the-cloud-new-azure-gs-vm-series?WT.mc_id=dx_MVP4000580

Important announcement in Azure space

 

Announcing VP9 support coming to Microsoft Edge

http://blogs.windows.com/msedgedev/2015/09/08/announcing-vp9-support-coming-to-microsoft-edge/?WT.mc_id=dx_MVP4000580

New feature for Microsoft Edge

 
 

Unity 5.2 and Visual Studio Tools for Unity 2.1

http://blogs.msdn.com/b/visualstudio/archive/2015/09/08/unity-5-2-and-visual-studio-tools-for-unity-2-1.aspx?WT.mc_id=dx_MVP4000580

Visual Studio is the new default Unity scripting editor on Windows

 

Announcing the Microsoft Azure Tour

http://azure.microsoft.com/en-us/blog/announcing-the-microsoft-azure-tour/?WT.mc_id=dx_MVP4000580

The Microsoft Azure Tour is a free one day technical training event for developers and IT professionals to help you achieve more success with Azure.

 

Visual Studio Code and Visual Studio Online

http://blogs.msdn.com/b/cdndevs/archive/2015/09/02/visual-studio-code-and-visual-studio-online.aspx?WT.mc_id=dx_MVP4000580

Blog on the integration with Git

 

I resisted learning and using keyboard shortcuts for more years than I care to admit. In recent years, however, I have come to appreciate just how valuable keyboard shortcuts can be in Excel and almost any other application you can name. The Blog ExcelTip has just published a detailed article about Function Key Shortcuts that is definitely worth a read if your are interested in expanding your shortcut key horizon.

Microsoft AzureCon Virtual Event September 29

Join Live Q&As and interact with the architects and engineers who are building the latest features… 

.Net Native

What .NET Native means for Universal Windows Platform developers

 

Windows 10 Developer’s Guide

The latest Windows 10 developer training contents

Microsoft Edge and Internet Explorer 11

How Microsoft Edge and Internet Explorer 11 on Windows 10 work better together in the Enterprise

Catherine Pope published this article, Evernote or OneNote – Which is Best?, today. In it she compares the two most popular notetaking applications. If you are trying to decide which is best for you, this article will be helpful.