Excel 2010 Financials: Adding Animations to Excel Graphs

Exclusive offer: get 50% off this eBook here
Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook — Save 50%

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook

$23.99    $12.00
by Andre Odnoha | June 2011 | Cookbooks Enterprise Articles Microsoft

Graphs are an important and essential method of demonstrating information visually for analysis and ease of understanding. Graphs in Excel, however, are static visual representations. You can filter the data or adjust the dataset to change the information that is displayed; however, the graph itself is still a static image. Adding animation visualization to a graph will add an element of professionalism to a presentation.

In this recipe by Andre Odnoha, author of Excel 2010 Financials Cookbook, you will learn how to add animation to Excel graphs.

 

Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

        Read more about this book      

(For more resources on this subject, see here.)

Getting ready

We will start this recipe with a single column graph demonstrating profitability. The dataset for this graph is cell A1. Cell A1 has the formula =A2/100. Cell A2 contains the number 1000:

How to do it...

  1. Press Alt + F11 on the keyboard to open the Excel Visual Basic Editor (VBE). Once in the VBE, choose Insert | Module from the file menu.
  2. Enter the following code:

    Sub Animate()

    Dim x As Integer

    x = 0

    Range("A2").Value = x

    While x < 1000

    x = x + 1

    Range("A2").Value = Range("A2").Value + 1

    For y = 1 To 500000

    Next

    DoEvents

    Wend

    End Sub

    The code should be formatted within the VBE code window as follows:

  3. Save your work and close the VBE.
  4. Once back at the worksheet, from the Ribbon, choose View | Macros | View Macros.
  5. Select the Animate macro and choose Run.

The graph for profitability will now drop to 0 and slowly rise to account for the full profitability.

How it works...

The graph was set to use the data within cell A1 as the dataset to graph. While there was a value within the cell, the graph shows the column and corresponding value. If you were to manually change the value of cell A1 and press enter, the graph would also change to the new value. It is this update event that we harness within the VBA macro that was added.

Sub Animate()

Dim x As Integer

x = 0

Here we first declare and set any variables that we will need. The value of x will hold the overall profit:

Range("A2").Value = x

While x < 1000

x = x + 1

Range("A2").Value = Range("A2").Value + 1
For y = 1 To 500000

Next

DoEvents

Wend

Next, we choose cell A2 from the worksheet and set its value to x (which begins as 0). Since cell A1 is set to be A2/100, A1 (which is the graph dataset) is now zero.

Using a "while" clause in Excel, we take x and add 1 to its value, then pause a moment using DoEvents, to allow Excel to update the graph, then we repeat adding another value of 1. This is repeated until x is equal to 1000 which when divided by 100 as in the formula for A1, becomes 10. Now, the profitability we end with in our animation graph is 10.

End Sub

There's more...

We can change the height of the profitability graph by simply changing the 1000 in the While x < 1000 line. By setting this number to 900, the graph will only grow to the profit level of nine.


Further resources on this subject:


Excel 2010 Financials Cookbook Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Andre Odnoha

Andre Odnoha has been working within the business and technology field for many years combining his knowledge of business and information technology. With his knowledge in both software and hardware related to computer systems and a Bachelor's degree in Business Administration, Andre works to combine and utilize technology from a business perspective.

Andre has over 11 years of experience in computer software and hardware and has programmed numerous software applications designed for medical billing, e-commerce, and content management.

Books From Packt


E-Business Suite 12.1 Financials Cookbook
E-Business Suite 12.1 Financials Cookbook

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation
Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Pentaho Data Integration 4 Cookbook
Pentaho Data Integration 4 Cookbook

Statistical Analysis with R
Statistical Analysis with R

iReport 3.7
iReport 3.7

Sage Beginner's Guide
Sage Beginner's Guide

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
T
u
y
G
F
A
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software