How to create interactive charts with radio buttons and a scroll bar

Ready to take interactive check boxes further? Check out Alesandra Blakeston’s post on Interactive Charts.

..and click here to view earlier post:  Automating the Spreadsheet using Check Boxes, DSUM 

User Friendly

Occasionally you need to deal with years and years of data across multiple data sets.  Seeing all of that data in one chart is distracting and difficult to read.  One of the best ways to get round that (IMHO) is to add some simple interactivity to your chart.

interactive chartObviously this is a static picture, but you can add scroll bars and check boxes to your excel sheet very easily.  I originally learned how to do the check boxes from Peltier Tech, the scroll bar I worked out for myself after seeing how the radio buttons worked, though I am sure he’s got the how to on there as well.

You can download the sample worksheet here.

Step 1: Prepare your data

First you need to work out what time range you want the chart to show.  I chose ten years, but you can set (almost) any time range you want.

View original post 1,058 more words

Create Names From Selection

Creating Defined Names enables you to take advantage of a host of excel features and shortcuts. This post shows one easy method to create Defined Names from your selection and showcases a nifty method to find where two Defined names intersect.

This tip involves naming cells, also known as Defined Names. Among the many benefits, Defined Names can be used to:

  • quickly navigate large spreadsheets
  • easily define print areas
  • simplify formula entry

In the below table you want to easily reference the data in any given column or row. Since the data is in a table you can easily create a Defined Name, for each column and row, using the Create from Selection command.

CreateNamesIntersect

Creating Defined Names From Selection

  1. Select all the cells in the table.
    Tip: select one cell, then press CTRL + A.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
  3. Check Top row and Left column.
  4. OK.

To display the Defined Names, press F3 or click the Name Box drop-down. Defined Names will also appear as you enter formulas, preceded by the name tag iconNametag.

 

PowerTip: The intersection of two Defined Names can be displayed using a formula.

Using Defined Names to Display the Intersecting Value

  1. In a blank cell, type =.
  2. Type the first Defined Name followed by a space.
  3. Type the second Defined Name and press Enter.

Cheers!

hɔuᴉnb

 
Watch this ~1m video to learn more.

Previous post and additional reading:

Column Select

Looking for an easy way to highlight a column that is not in a table? Use COLUMN SELECT

Note: This tip works equally well when editing an email in Outlook.

Looking for an easy way to highlight a column that is not in a table? Use COLUMN SELECT

ColumnSelect
Click to enlarge

Column Select

  1. Click insertion point at begining of text
  2. Press ALT as you CLICK + DRAG to end point.

Once selected the text can be formatted or deleted. The selection collapses after your executed command.

Be smooth:  ALT+CLICK instruction brings up the Thesaurus*, so be don’t click quickly. Column Select (ALT + CLICK + DRAG) works best to when you use a smooth, paint-like motion (more like Pollock than Seurat)

* (a deadly neolithic creature hellbent on correcting ingesting your text and regurgitating its own).

Watch this 1m video for more.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

Related:

A revolutionary drone-based delivery network is being tested—in Bhutan

While Amazon’s drone plans stall, Silicon Valley startup, Matternet, is piloting (pun intended) a drone service in Bhutan. This aerobo (aerial-robotic) fleet is providing a means of delivering medicinal supplies to rural communities.

Display Multiple Time Zones

Negotiating meeting schedules between time zones can be tricky. Here are two simple methods to help keep track of time in other regions:

Add Additional Time Zone to the Outlook Calendar

DualTimeZones

  1. In Outlook, click File, then select Options. The Outlook Options dialog appears.
  2. At left, select Calendar, then scroll down to the Time zones section.
  3. In the Label area type a brief description for the local time zone.
  4. Check Show a second time zone.
  5. In the second Label area, type a brief description for the second time zone.
  6. Select additional time zone from the second Time zone drop down.
  7. OK.
OutlookOptionsCalendar
Outlook Calendar Options

To Add Time zones to Windows Taskbar 

The windows taskbar has the ability to show two additional time zones.

TimeClocks
Windows 8 Time Display
  1. RIGHT + CLICK on time displayed in lower right corner of taskbar.
  2. Select Adjust date/time.
  3. Click Additional Clocks tab.
  4. Check Show this clock.
  5. For each additional time zone, check Show this clock, select time zone from the drop-down, then enter a name in the field below.
  6. OK.
Additional Clocks dialog
Additional Clocks dialog (CLICK to enlarge)

 

For additional multi-regional scheduling resources, visit the World Clock website. There you will find meeting calculators, daylight savings information and interactive maps.

Additional reading..

Cheers!

hɔuᴉnb