Charts from a dropdown list

Charts from a dropdown list in Excel

Many people who use Excel can generate a chart or two on their spreadsheets. This is a relatively easy task to perform in Excel. These charts will either sit in data sheets or in their own sheets.

Don’t you sometimes wish you had a button to click in Excel which gives you a list of charts to display on your sheet? Well, I can show you how to do this!

First of all, you will need some charts. It doesn’t matter which types of charts they are. The screenshot below shows an example of a sheet containing data and charts:

Charts from a dropdown list

Once you have your charts up and running, you can begin to build your interactive button. The first thing you need to do is to select the cells which are behind the first chart, as shown below.

Charts from a dropdown list
Sales+Costs Chart

After doing this, click in the Name box (top left next to the Formula bar). In here, give this selection of cells a name, for instance Sales. Do the same for all the other charts you wish to add to the list. These names will be used later to choose the right chart from the list. Now, in a different place on the sheet, or in another sheet, type a list of your chart names you want to display in the dropdown box (see my example below ).

Charts from a dropdown list

Select this list and give it a name (in the Name box again), as shown above. This name will be used to generate the dropdown list. We are now ready to create the dropdown button. Add a new sheet to the workbook and name it Output. Activate the Developer tab in the ribbon. Click File – Options – Customize Ribbon, then tick the box next to Developer in the right hand side box. Select the Developer tab in the ribbon, then click the Insert button.

Charts from a dropdown list

This opens a toolbox from which you select the Combo Box item. Now, in the Output sheet, draw the Combo Box near the top of your sheet with your mouse. Afterwards, press Ctrl+1 (format control). A dialogue box opens as shown below:

Charts from a dropdown list

Click in the Input range box and then type the name which you gave earlier to your list of charts  (I called mine ChartTypes). Next, click in the Cell link box, then click in any empty cell in your spreadsheet. This cell reference now sits in the Cell link box. We shall use this cell later to pick a chart.

The next thing we need to do is to define a name which uses Excel’s Choose function to pick the correct chart from the list.

Step 1: Select the Formulas tab, then select Define Name.

Step 2: Type SelChart into the Name box. (SelChart is my example)

Step 3: Type the following into the Refers To box:    =choose(                                                            Now select the cell you selected for the Cell link box earlier. Next, type the names you gave your charts in the Name box.

=choose(selected_cell,Name1,Name2,Name3)  (selected_cell is the cell you used in the Cell link box earlier. The other names are the names you gave the cell ranges behind your charts.)

Step 4: Click OK.

Almost there! We now need to again select the cells behind each chart, (one chart at a time), then right-click the selection. In the dialogue box, click Copy, then go to your Output sheet. Right-click below your dropdown combo box and select Paste Special – Linked Picture. (See below)

Charts from a dropdown list
Paste Special

Hopefully, you now have an image of your chart in that location on your sheet. Click in the formula bar and type =SelChart (again, this is my example). Press Enter. Do this for all your charts. Now you can create Charts from a dropdown list.



What’s new in PowerPoint 2016

What’s New in PowerPoint 2016

What's new in PowerPoint 2016
What’s new in PowerPoint 2016

If you are currently using Powerpoint 2013, you will know what a big improvement it is on earlier versions, with some great new features such as the ability to insert pictures and videos directly from websites such as Bing Images and YouTube, and the Merge Shapes tool which allows you to create custom shapes using two or more existing shapes. It is also possible for people to co-author presentations in the 2013 version if you have SharePoint or Office Online. Another improvement is increased functionality in Presenter View.

Well, news from Microsoft is that a new version of Office will be released during Autumn 2015. Microsoft call it Office 2016. There is currently a preview version available for download, which will expire when the official version is released. Microsoft provide a product key for those who wish to download this trial version. The link is:

We have had a look at the applications included in Office 2016 and here are some of the new features to be found in PowerPoint 2016:

Tell Me Box

The most obvious new feature is a box in the ribbon which says: “Tell me what you want to do…” This saves you the time you would normally use to look for a specific function within the Ribbon. You can type what you’re looking for in the Tell Me box at the top right corner of the Ribbon, and with “word-wheeling”, which enables results as soon as you start typing, every keystroke refines the results so that you can click on what you’re looking for as soon as you see it.

For example, when you want to change the layout of your presentation, simply type “layout” and Tell Me will give a view of the different slide layouts available. Also, when you click in the Tell Me box, you’ll also see a list of the last five commands you ran from within Tell Me, saving you time getting to the feature you want to use.

Ppt Screen

Real-time co-authoring in PowerPoint

You can now access the presentation sharing function by clicking on the “Share” button next to “Sign in” to the right of the ribbon tabs. Note that in order to share a presentation, it has to be saved to a cloud server or a shared drive. In PowerPoint 2016 you will be able to collaborate on a presentation in real-time. This feature will have built-in technology which simplifies resolving conflicts when co-authoring. You can choose between two versions of the conflicting slides, your changes or others’ changes, instead of sifting through the individual conflicts.

Quick Shape Formatting

This feature increases the number of default shape styles by introducing new “preset” styles in  PowerPoint. The preset styles allow you to choose the intensity of the colour you want to apply, ranging from colouring just the border to filling the shape with very intense colouring.


Save As Improvements

These improvements streamline the saving process for new files by enabling you to pick a location in OneDrive, OneDrive for Business, or on your local machine, provide a file name, and then click Save. Saving a file is now as easy as creating one, and it can all be done in the Backstage in PowerPoint.

Insights for Office – Smart Lookup

Already available in Word and Outlook, this is now available in Excel and PowerPoint and helps you learn more about your content by bringing you precisely the right information in context of what you are reading or writing about. With information from a variety of sources like Bing Snapshot, Wikipedia, Bing image search and Oxford dictionary, you can do anything from a quick look up to a detailed exploration without leaving the Office application. Smart Lookup can be accessed by right clicking a word or phrase, through the Review tab on the ribbon, or via the Tell Me box. Insights is powered by Bing and uses the selected text and some surrounding content to get contextually relevant results.


Tabs and Ribbon Colours

Joy of joys, PowerPoint 2016 has some great application colour schemes built into Account settings. You can choose from colourful, dark gray, black or white. A big improvement on 2013! Below is a view of the dark gray colour scheme applied to the PowerPoint user interface.


Touch screen friendly

You may have noticed the “Double tap” suggestions inside placeholders on the slides in this article. This means that PowerPoint 2016 is designed as much for conventional PC’s as it is for tablets and other touch screen devices able to run Office 2016.

New Chart Types

In addition to the existing charts available to use in PowerPoint, there are five new chart types in 2016: Treemap, Sunburst, Histogram, Box & Whisker and Waterfall. These will of course all be compatible with Excel 2016.


Embedding video code

PowerPoint 2016 now has an easy-to-use and more reliable tool to embed video code links from websites such as YouTube. Simply copy the Embed code from the website, then click Insert – Video – Online Video. Paste the code in the box next to “From a Video Embed Code”. See below.


Apart from these new features, PowerPoint 2016 retains all the tools which have made the previous version great.