Tag Archives: chart

excel to image

Excel to Image – VBA Save Range or Workbook as Image

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

How to save an Excel Range as an Image file (JPG)? Today’s post is inspired by my readers emails. In some of my previous emails I considered other Excel Image scenarios such as Adding Images to Excel using VBA or using the Excel Camera Tool to create an image snapshot in Excel. However today I stumbled across a different scenario: Saving an Excel Range as an Image File.

Excel to Image: Save Excel Range as Image

Excel Range to Image
Excel Range to Image
Let us start with an example. The easiest way to create an image in Excel is to Select an Excel Range, next hit the Copy button from Home ribbon and finally by clicking Paste with Picture.

Seems at first like we are just one step away from saving the image as an image file right?

Excel: Right click only to find... there is no Save As Image
Excel: Right click only to find… there is no Save As Image
Wrong! Only problem being… there is no Save As Image button easily available in Excel!

Of course you might say – why no copy and paste to MS Paint or another Image Editor? Yes that is always an option. But let me show you a much better and dedicated tool for taking Snapshots in any Windows application. Next I will show a way to achieve the task above with a simple VBA Macro.

Snipping Tool

Windows Snipping ToolOne way is to use the almighty Microsoft Snipping Tool which is great for creating image snapshots and saving them as images (PNG files).

Windows Snipping Tool Example
Windows Snipping Tool Example
You can see how the Snipping Tool works easily.

The problem however is that the Snipping Tool is not very precise and often detailed Excel Range images are cumbersome to achieve. This is where as usually we can use a bit of Visual Basic for Applications to automate this task…

Excel to Image with VBA

Now let us create an Image from an Excel Range like a pro. First copy the Excel VBA code below to an existing or new VBA Module:

What does the VBA Macro do? The Sub will do the following

  1. Copy the selected Excel Range and paste it as a Picture

  2. Copy the Picture to an empty Chart

  3. Ask for the destination file name

  4. Save the Chart as a JPG image

Now you may as Why not use VBA to save the pasted Picture as an Image file?. Well, you can’t (not so straight forward at least). The workaround above works pretty well however.

Using the Excel to Image VBA

run excel to image macro
Select Excel Range and open Macros
First select the Excel Range you want to save as an Image. To run the VBA Macro click the Macros button to open the Macros window.

run the excel to image macro 2
Select the VBA Macro and click Run
All that is left is to select the VBA Macro from the Macro window and to hit Run.
excel to image save window
Lastly name your destination file and hit Save
There may a short pause for the macro to process the image, shortly after the Save As file dialog should appear. Simply select your destination file name and hit Save and that is it!

Save Excel as Static Image Workbook

As usual I am leaving the best for last. Say you want to share an Excel Workbook as readonly. You can try protecting the Password Protecting your Excel documents but the safety is limited if you want to protect your underlying formulas.

excel to image vba macro
Save your Excel Workbook as a Static Image Workbook
What better way to protect your formulas then to send an Excel Workbook with print screen images of each and every Worksheet? The VBA Macro will additionally save the copy of your file as an XLSX file which means all VBA Macros will be removed as well.

Excel to Static Image Workbook VBA

Below the VBA code snippet to create a copy of your Workbook where every Worksheet is an image copy of the original.

Great right? I have noticed that in most cases the Image Workbook might however be a bit larger than the original file which has its slight downside. Then again this will depend heavily on the type of Workbook (more formatting larger file size).

Let me know what you think!

excel pictograph featured

Excel Pictograph – Charts with Pictures

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Today something much lighter than usual, more to do with presenting data than just crunching itHow to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images!

Images are Powerful Stuff! Our Brain processes Images 60 Thousand Times Faster than Text! For us Consultants, Marketers and Office Clerks this is important to understand, although often neglected. Hence the term Death by PowerPoint, known also as Death By Slideas in overwhelming your audience with an abundance of text and information squeezed into a single slide.

If you want to get Your Message across use Pictures instead. Today I will show you a simple, but effective Excel Trick you can use to make the most out of your Data Visualizations – to transform Dull Charts into Impressive Infographics.

How to Create an Excel Pictograph?

example excel pictographLet’s start with the Basics. An Excel Pictograph is a Chart that replaces standard Bars, Pies and other Graphic Elements with Pictures. Creating Excel Pictographs is incredibly easy, hence making it an incredibly Powerful Excel Tip / Trick.

Create an Excel Chart

To create an Excel Pictograph we need to have a Chart to work on first. If you are new to Excel Charts feel free to listen to Chandoo’s Excel Chart Tutorial here. I created a simple Chart below:

Simple Boring Excel Chart
Simple Boring Excel Chart

Find an Image Icon for the Chart

The Chart above is Boring. Isn’t it? Just Raw Data. It takes a moment to take it in. Now let’s find an interesting image to replace the Boring Barchart! Here are some great resources I use to find Image Icons:

  1. IconFinder a great database of Free and Premium Image Icons. I really appreciate the large amount of free images they have you don’t need to link back to

  2. Icon Archive a clipart image database

  3. Icon Finder yet another icon database

Be sure to find an Icon that will suitably visual the data you are trying to exemplify.

Paste the Image to your Excel Spreadsheet

past image to excelNow copy your image to your Excel Worksheet. To do that go to the INSERT Ribbon Excel tab. Select Pictures if you have the Image on your workstation, or Online Pictures if you need to download the Image from the Web. Follow the steps and hit Insert.

Copy & Paste the Image onto your Chart

This is the important part:
past image to excel chart

  1. Copy the Image by clicking on it and hitting CTRL+C

  2. Select the Data Visual select your Charts Bars, Pie slices or any other Data Visual Element which you want to replace with an Image

  3. Paste the Image by hitting CTRL+V

Configure the Image

The Chart is starting to look good. But often stretching the Image distorts it. We need to replace the Stretch with either the Stack or Stack and Scale with options:
excel pictograph configure image settings

  1. Go to Data Series options by right clicking on the Bars and clicking Format Data Series...

  2. Go to Fill Options and select Stretch, Stack or Stack and Stretch with. That last options requires you specify the Units/Pictures ration. In our case 1000$ should equal to 1 picture, so I input 1000

Too make the Image look even better, you may also want to go to SERIES OPTIONS and adjust the Gap Width!
The Final Effect looks more like this:
final excel pictograph

dynamic excel charts

Use Form Controls to make Dynamic Excel Charts (no VBA)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Dynamic Excel charts are so much appealing to users and more pleasant in interaction. Fortunately Excel has PivotCharts and Slicers. But what if you want to have more control over your charts and use other controls such as Dropdowns, ScrollBars and Radiobuttons? Today I will show you how to easily use Form Controls to add interactivity to your Excel Charts without any VBA nor PivotTables/PivotCharts.

Form Controls

What are Form Controls? Form Controls can be accessed from the Developer ribbon in Excel (File->Options->Customize Ribbon). They are simple controls that can be dragged-and-dropped onto your Worksheets and easily configured.

Excel Form Controls
Excel Form Controls

What makes Form Controls special? They are configurable and can be linked to cells in your Excel Workbook. Look below for an example of how the ScrollBar properties look like. Notice especially the Cell Link property which will output the current value of the Form Control (in this case the scroll value). This opens a whole lot of different possibilities to use Form Controls in calculations/charts etc.
Example Form Control Properties
Example Form Control Properties

Dynamic Excel Charts with a ScrollBar

Let’s consider a first example with a ScrollBar.
On the left there is a simple table with 3 columns:

  • Company – name of a company
  • No. of employees – number of employees in the company
  • Total – the number to be presented in the chart cut off by the ScrollBar (only showing values above)

Notice that the Total formula take the value from the ScrollBar to set the current cut-off. By scrolling the ScrollBar notice that the formulas will automatically recalculate and the chart will refresh to show only values above the cut-off. Neat huh?

Excel Dynamic Chart with ScrollBar
Excel Dynamic Chart with ScrollBar

I tend to usually overlay the Form Controls over the chart itself so it is more obvious for the user that the chart is dynamic.

For Excel 2013 users – you can also use timeline scrollbars natively in PivotTables/Charts (available on the Analyze ribbon tab).

Dynamic Excel Charts with a Dropdown

Let’s now consider a different example using a Dropdown form control.
Again on the left side notice the data source table. I have simply split the employee column into two sections: IT employees and non-IT employees.

We will also create a dropdown with 3 options:

  • IT – sum only IT employees
  • Non-IT – sum only non-IT employees
  • All – sum all employees

Having these three categories we can link them to the Dropdown control so they appear when click on the bottom arrow. Simple right? Now let’s link the H2 cell with the Dropdown control value and we can now see the changes in the selection of the Dropdown. Notice that the Total cell formula with show different value depending on the selection in the Dropdown. Easy right?

Example Dropdown Form Control
Example Dropdown Form Control

Again I would recommend overlaying the chart with the Dropdown to make it seem like a natural part of the Excel Chart.

Dynamic Excel Charts with a Radiobuttons

Radiobuttons work similarly as Dropdowns with the difference that you have to group them using the GroupBox control to achieve mutual exclusion. See the example below:

Example FormControls Radiobutton
Example FormControls Radiobutton

Next steps

Excel Google Charts Tool – Learn how to add cool Google Charts to your Excel Workbook

map

Excel Google Charts Tool

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

I always wanted to utilize the beautiful and interactive Google Charts in Excel. The Google Charts repository is constantly growing and sometimes Excel lacks those features. Hence I introduce the Excel Google Charts Tool to display a way to leverage some of those Google Charts directly in Excel.

The WebBrowser control is no longer supported by Office 2013 and above, hence this functionality might not work by default in those versions of Office

The Excel Google Charts Tool contains example Google Charts embedded inside an Excel xlsm file allowing you to visualize data in a more attractive way and enabling more user interaction.

Excel Google Charts: Gauge Chart

Gauge charts are extremely useful to highlight important values in reports. You can also visualize the good and bad ranges of values e.g. orange and red for too high values. These however, can be easily configured. I

Excel VBA Gauge Chart
Excel VBA Gauge Chart

How to configure a Gauge Chart?

Google Chart: Gauge Chart

Excel Google Charts: Treemap Chart

Treemaps can be particularly useful when you want to drill-down data values e.g. used disk space and drill-down across folders. Google Treemaps have 2 values which you can visualize – the area of the treemap and the color.

Excel VBA Treemap Chart
Excel VBA Treemap Chart

How to configure a Treemap Chart?

Google Charts: Treemap Chart

Excel Google Charts: Org Chart

Excel VBA Orgchart
Excel VBA Orgchart

How to configure a Org Chart?

Org Charts come in handy when you want to visualize the tree/organisational structure.
Google Charts: Org Chart

Excel Google Charts: Geo Chart

I would say – one of the most useful charts when playing with geo-data. Using the Geo Chart you can easily visualize how your data is broken down across countries. You can zoom in the Geo Chart just to show a single continent, country or region.

Excel VBA Geochart
Excel VBA Geochart

How to configure a Geo Chart?

Google Charts: Geo Chart

Download

The file below contains all examples of Google Charts used in the Excel Google Charts Tool.


Currently the Google Chart Tool contains examples of the following Google Charts:

Issues and errors

One issue you might stumble on when using the above Google Charts may be due to recent scriptable control restrictions imposed by Microsoft. Due to these in Excel 2013 and above Excel will restrict (by default) the use of some controls e.g. Microsoft Web Browser Control – which is required to run the above Google Charts. There is a way around that so utilize the link above to read more.