All articles in 'Excel Howtos' Category

Create PowerPoint Presentations Automatically using VBA

Published on Aug 3, 2011 in Charts and Graphs, Excel Howtos, VBA Macros
Create PowerPoint Presentations Automatically using VBA

You’ve been there before. It’s almost 5:00, and you are going crazy trying to finish the presentation due for a monthly performance meeting the next morning. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course her change means you have to start all over with the copy and pastes…

There is always a better way! In the Oil and Gas industry, I constantly have monthly reports to assess the performance of our operating assets. Excel VBA makes it a cinch to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!

Continue »

Get busy this weekend, with OR XOR AND [Excel Homework]

Published on Jul 29, 2011 in Excel Challenges, Excel Howtos
Get busy this weekend, with OR XOR AND [Excel Homework]

Some of you have heard my neighbor’s dog bark in a video or two that I recorded. While I was busy explaining how to do something awesome in Excel, this dog would decide to bark, adding her own two cents to the lesson. Quite a few of my VBA class students have grown used to it. So much that they complain when a lesson doesn’t have a couple of woofs. But I digress.

So coming back, one of the dogs (probably stray) has decided that she should bring her infant puppies and hide them under our terrace stair case. So, now we have 2 cute little puppies barking day long (and very late in to the night) just outside my office window. We have tried hard to get rid of them, but they somehow sneak back in and start barking or crying. So, I will be busy this weekend trying to move them out.

But that doesn’t mean, you have to live Excel-less for a few days. So I have a homework.

OR XOR AND, Get busy this weekend!

Don’t worry. I am not speaking elvish or something. OR, XOR & AND stand for bit-wise operations. This week, your task is to write formulas in Excel that would get the bit-wise results for AND, OR & XOR.

Continue »

Accumulated Depreciation using Mixed References

Published on Jul 19, 2011 in Excel Howtos, Financial Modeling
Accumulated Depreciation using Mixed References

Last time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be using the sum function! Yes you heard it right – The Sum function.

But we would use the Sum function with a small trick! We would use it to calculate running cumulative sum! And believe me, you would need this function so many times – to calculate accumulated depreciation, cumulative debt, Profits to Retained Earnings and almost all the accounts that would consolidate into the balance sheet.

Continue »

Create Dynamic Hyperlinks in Excel [Video]

Published on Jul 14, 2011 in Excel Howtos, Learn Excel
Create Dynamic Hyperlinks in Excel [Video]

During one my recent training programs, a participant asked an interesting question.

I have a list of values. I would like to place a hyperlink in my workbook that would always take me to the last value in the list.

Something like shown aside.

Click on the image to learn how this is done.

Continue »

Sumproduct function to Consolidate Revenues?

Published on Jul 12, 2011 in Excel Howtos, Financial Modeling

Chandoo.org is all about simplifying life using Excel. When I first started visiting the site, I was amazed at the amount of information on the site, which made your life easier. My next few posts would be about excel functions which can make your life for Financial Modeling easier! These are simple examples that you […]

Continue »

Automating Repetitive Tasks

Automating Repetitive Tasks

Over at Newton Excel Bach I spotted the Dynamically Defined Dancing Pendulums. Doug had used Strand7 for the animation.

I thought I could do that in Excel and did.

This post demonstrates some of some basic VBA techniques used to simplify repetitive tasks used during the construction of the project.
For a full project description visit Excel Hero.com.

Continue »

Analyse Data like a Super Hero

Published on Jun 20, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Analyse Data like a Super Hero

Can I automate 500 Columns of Data Analysis without VBA?

Absolutely!

Excel super function – Data Table to the rescue.

After reading this post, you will be able to process data like a Data Analysis Super Hero !

Continue »

Comparing Lists of Values in Excel using Array Formulas

Published on Jun 14, 2011 in Excel Howtos, Learn Excel
Comparing Lists of Values in Excel using Array Formulas

Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.

Continue »

Advanced Sumproduct Queries

Published on May 26, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Advanced Sumproduct Queries

How often have you had a simple table where you want to lookup a value or add up some values meeting a criteria?

This post looks at a simple way to do a 2D Lookup or 2D Sum of a values from within a 2D range with multiple criteria, without complex Sum, Offset, Index, Match, Sumifs or other functions, simply using Sumproduct

Continue »

Lost Excel Functions

Published on May 16, 2011 in Excel Howtos, Huis, Posts by Hui
Lost Excel Functions

- Cue, alluring music.
Just like Jack and the crew of Oceanic Airlines flight 815, Excel has a number of LOST functions.
This post will look at some of Excel’s Undocumented, Unloved and Rarely used Functions

Continue »

Using Solver to Assign Items to Buckets

Published on May 11, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Using Solver to Assign Items to Buckets

Ok, You’ve just finished the mother of all Spreadsheets. Not only does it solve your companies budget, it tracks production, reports variances, makes a mean cup of coffee and some say, “it May Even Solve World Peace ?”.
But the boss walks in and asks “I have a list of items which I need to distribute evenly into colored buckets”
I had never thought of that option, Maybe I can constrain my model and let it work it out for me ?
Solver I need your help !

Continue »

Dummy Data – How to use the Random Functions

Published on May 4, 2011 in excel apps, Excel Howtos, Huis, Learn Excel, Posts by Hui
Dummy Data – How to use the Random Functions

Don’t know which way to go ?
Can’t make up your mind between alternatives?

Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model. This post will look at some options for setting up Dummy Data using Excels Random number functions.

Continue »

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

Last week Chandoo presented Give more details by showing average and distribution

At the top of the post was a small screen capture from Amazon.com showing a 5 Star chart showing that Twilight had a 3.5 Star Rating (way over-rated if you ask me).

I received an email shortly afterwards from Rajiv, “How can I make one of those charts ? ” with the Stars Circled

It’s actually very simple and this post will show you how.

Continue »

Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]

Published on Apr 11, 2011 in Excel Howtos
Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]

For most of us, the prospect of inheriting a large, undisclosed sum of money is bleak. But we have high probability of inheriting a complex Excel workbook with 19 worksheets and 2300 rows of data and 195 formulas. The kind where entire rainbow colors are used to color code accounts receivable statuses. Then what do we do? We spend a whole afternoon (and then the rest of the month) breaking our head trying to figure out why the total revenues are only $ 41.2 million when profits are $ 99.23 million.

So how do we deal with our inheritance?

Here is a quick tip to help you get started. Disable “Direct editing mode“.

Continue »

Beam Me Up Scotty – Excel Hyperlinks

Published on Mar 31, 2011 in Excel Howtos, Huis, Posts by Hui
Beam Me Up Scotty – Excel Hyperlinks

Ok, Excel Hyperlinks may not be able to rescue you from an imploding planet, but they can add to the useability of your Excel Projects.

This post looks at Hyperlinks. What they are and how to use them in your workbooks

Continue »