Ads 468x60px

Saturday, June 30, 2012

Learn Any Area of Excel using these 80 Links

Learn Any Area of Excel using these 80 Links:
Last week I asked, What is one area of Excel you want to learn more?
More than 250 of you responded to this question. Many of you shared your areas of interest thru comments, quite a few of you also emailed me personally.

So what next?

You told us what you want to learn, the next step is logical. We share some of the best tutorials & examples with you so that you can learn. In this post, we have presented more than 75 links, to help you learn your area of focus.
I have divided this in to 16 areas. In each area, we have identified (upto) 5 best links for you to learn more. I have also recommended 1 or 2 training programs that make you awesome in that area. Plus, if we found any excellent external resources, we have highlighted them as well.
So go ahead and learn Excel.

Share your links

If you come across any good resource for learning Excel, please share it with us. I am always looking for ways to learn more. So go ahead and drop a comment.

Thanks to Hui

Special thanks to Hui, for compiling the survey results & some of the links.
Posted by at 0 comments

Formula Forensics No. 005 – Zebras and Checker-Boards

Formula Forensics No. 005 – Zebras and Checker-Boards:
This week in Formula Forensics we’ll look at, Zebra Stripes and Checker-board Conditional Formatting.
This idea is inspired by a number of posts over the past few years asking about zebra stripes but specifically BobR who in in June 2011, also asked about Checkerboards in the post: Want to be an excel conditional-formatting Rock Star, Comment No. 154.
I got the conditional format for alternating row and column colors,
Is there a conditional format to make it a checkerboard whereas the cell A2 will remove either the conditional for the row or column and then alternately to A4, B1, B3 etc?



Chandoo responded fairly quickly with this Conditional Formatting formula:
=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)
Unbeknownst to Chandoo I posted this about a minute later:
=ISODD(ROW()+COLUMN())
Both formula correctly answer BobR’s question.
So today we’re going to pull apart Zebra Stripes and Checker Boards and see what makes them tick.
As always you can follow along in a download file here: Download File.


ZEBRA STRIPES

Zebra Stripes as Conditional Formatting is simply applied using a simple formula within Conditional Formatting.
=MOD(ROW(),2)=0
Conditional Formatting requires a formula that returns a boolean “True” to apply a format or a Boolean “False” to not Apply a format.
So the formula is better read as: If MOD(ROW(),2)=0
And  If MOD(ROW(),2)=0, the formula will evaluate as True
This is best evaluated as 3 columns on a worksheet.

In cells
B5:B10 The formula =Row() returns the Row Number
C5:C10 The formula =Mod(Row() ,2) returns the Mod of Row Number, divided by 2
The Mod function returns the remainder of the division of the Row Number divided by 2,
So in Row 5, Mod(Row(),2) = Mod(5, 2) = 5/2 = 2 Remainder 1 = 1
and in Row 6, Mod(Row(),2) = Mod(6, 2) = 6/2 = 3 Remainder 0 = 0
D5:D10 The formula =Mod(Row() ,2)=0 checks the remainder against the value 0
This is what evaluates to either True or False depending on the Row number.
Where the Values are True the Format will be applied (Even Rows)

The Conditional Formatting can be applied to Odd Rows If the Formula is slightly altered
=Mod(Row() ,2)=1


Similarly the formatting can be applied to Columns using
=MOD(COLUMN(),2)=0/1



CHECKER BOARDS

RobR received two responses to his Checker-Board Conditional Formatting request.
=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)
and
=ISODD(ROW()+COLUMN())
Lest see what’s inside these two formula.

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2) =0, MOD( (ROW() -1)*8+COLUMN(),2)=1)

This is a simple If Formula with 3 components
=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)
If Condition        MOD(ROW(),2)=1
Value if True:     MOD((ROW()-1)*8+COLUMN(),2)=0
Value if False:    MOD((ROW()-1)*8+COLUMN(),2)=1
The If Condition is already known to us, as it’s the same formula used in the Zebra Stripes above.
It evaluates to True when it is on an Odd Row.
So when it is an Odd numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=0
And when it is an Even numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=1
We can notice that these are the same formulas which have a different ending of =0 and =1
MOD((ROW()-1)*8+COLUMN(),2)=0
This section Takes each Row subtracts 1 and then multiplies this number by 8. This can be expressed as simply as saying multiply the Row * 8.
This will always return an Even Number and could have been simplified to Row()*2
MOD((ROW()-1)*8+COLUMN(),2)=0
The next bit adds the column number to the previous Even Number.
So now this part will be Odd when the column is Odd and Even when the column is Even.
MOD((ROW()-1)*8+COLUMN(),2)=0
The remainder of the formula is the same as the Zebra Stripes formula.
An Odd Number (Odd Columns) in the section above will return a 1 as the result of =Mod(Odd,2)
An Even Number (Even Columns) in the section above will return a 0 as the result of =Mod(Odd,2)
When evaluated against 0 will return True for Even Columns and False for Odd Columns.
Now the exact same happens in the False section of the If formula except that it is evaluated against 1.

=ISODD(ROW()+COLUMN())

I tackled this problem from a different direction to Chandoo.
Knowing that Even + Even = Even and Even + Odd = Odd and that the row and Column Numbers increase in each direction by 1 each Row/Column, it was simply a matter of adding the Row and Column numbers together and checking if it was Odd or Even
The Excel function IsOdd() and IsEven() both return a Boolean “True” if the contents are Odd or “Even” respectively. This negates an external truth check as described above.
This is easily shown by adding a formula to the Checker area
=Row()+Column()

Excel 2003: The above formula won’t work in Excel 2003.
Try this instead =Mod(Row()+Column(),2)=1



If the alternate shading is required a switch to
=ISEVEN(ROW()+COLUMN())
Does the trick.

Excel 2003: The above formula won’t work in Excel 2003.
Try this instead =Mod(Row()+Column(),2)=0



Learn More About Conditional Formatting Here:

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
and
http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
and
http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

DOWNLOAD

You can download a copy of the above file and follow along, Download Here.

OTHER POSTS IN THIS SERIES

You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensics 001 – Tarun’s Problem
Formula Forensics 002 – Joyce’s Question
Formula Forensics 003 – Lukes Reward
Formula Forensics 004 – Freds Problem

We Need Your Help !

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or this post.
If you have a formula that you don’t understand and would like explained but don’t want to write a post also send it in to Chandoo or Hui.



Posted by at 0 comments

Introduction to Spreadsheet Risk Management

Introduction to Spreadsheet Risk Management:
This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit
  • Part 1: An Introduction to managing spreadsheet risk
  • Part 2: How companies can manage their spreadsheet risk
  • Part 3: Excel’s auditing functions
  • Part 4: Using external software packages to manage your spreadsheet risk
Introduction to Spreadsheet Risk Management

An Introduction to managing spreadsheet risk

The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive.
See the full news article here: http://www.express.co.uk/posts/view/276053/Mouchel-profits-blow
Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.

Why do we do what we do in spreadsheets?

Because we all love Excel, right? True certainly, but the main reason is that it is intuitive, flexible, cost effective and provides quick solutions to high priority day to day problems.
And what is the alternative? The IT department. The simple fact is that end user developed spreadsheets often fill the gap between the current business requirements and formally managed IT systems.
Unfortunately this reliance on spreadsheets, rather than robust, well governed IT solutions can add significant risk to an organisation if it is not properly managed.

So what is spreadsheet risk?

Spreadsheet risk is the risk that a business could lose revenue and profit, fail to comply with regulators or find its reputation damaged as a result of spreadsheet error (be it fraudulent or unintentional).
Poorly structured spreadsheets can also lead to a loss of productivity and increased audit costs, further damaging the bottom line.
A recent study of typical enterprise spreadsheets by the Tuck School of Business at Dartmouth found that 94% of spreadsheets and 5% of all formulae within spreadsheets contain errors.

Some further examples of the impact of failing to manage spreadsheet risk

The European Spreadsheet Risk Interest Group (EuSPrIG) are the voice of best practice spreadsheet development and the management of spreadsheet risk.
Below are a couple of examples of what can go wrong from the EuSPrIG website:
C&C Group admit to mistake in revenue results
Shares in C&C fell 15 per cent after it said total revenue in the four months to end-June had not risen 3 per cent as reported, but had dropped 5 per cent. C&C said cider revenues in the UK had fallen 12 per cent, not 1 per cent, while cider revenues in Ireland were flat instead of up 7 per cent as reported last week.
C&C’s group finance director and COO said the error in last week’s announcement occurred after data were incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement. “It was basically human error… there’s nothing wrong with our accounting systems,”
FSA fines Credit Suisse £5.6m
The FSA decided to impose a financial penalty of £5.6 million on the UK operations of Credit Suisse in respect of a breach of Principles 2 and 3 of the FSA’s Principles for Business:
  • Principle 2 states that “A firm must conduct its business with due skill, care and diligence.”
  • Whilst Principle 3 requires that “A firm must take reasonable care to organise and control its affairs responsibly and effectively, with adequate risk management systems.”
More specifically, section “2.33.3. The booking structure relied upon by the UK operations of Credit Suisse for the CDO trading business was complex and overly reliant on large spreadsheets with multiple entries. This resulted in a lack of transparency and inhibited the effective supervision, risk management and control of the SCG {Structured Credit Group}”

Conclusions

Excel is, and is likely to remain, the first choice for businesses when developing financial models and analysing data. The risk that this introduces to businesses if unmanaged is real and potentially material.

What next?

In the next article we will look at ways that companies can manage their spreadsheet risk.

Added by Chandoo

In my brief usage of Excel, I have experienced several risky situations. Sometimes it just a mild data loss, other times, there was a potential of revenue loss or customer annoyance. Due to the economic slowdown many large and small corporations are employing spreadsheet based solutions. And if you do not understand the risk & manage it, then your risk being featured on EuSPrIG’s horror stories page.

What about you?

Do you know (or experienced) a spreadsheet horror story? Please share your ideas and best practices with us using comments.

Thank you Myles

Many thanks to Myles for writing this series. Your experience in this area is invaluable. I am really keen to learn about the best practices and adopt them in my business. If you enjoy this series, drop a note of thanks to Myles thru comments. You can also reach him at Excel Audit or his linkedin profile.
Posted by at 0 comments

8 Tips to Make you a Formatting Pro

8 Tips to Make you a Formatting Pro:
We  can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

1. Use tables to format data quickly

Introduced in Excel 2007, Excel Tables are an incredibly powerful way to handle a bunch of related data. Just select any cell with in the data and press CTRL+T and then Enter. And bingo, your data looks slick in no time.
Use tables to format data quickly
Learn more about Excel Tables.

2. Change colors in a snap

So you have made a spreadsheet model or dashboard. And you want to change colors to something fresh. Just go to Page Layout ribbon and choose a color scheme from Colors box on top left. Microsoft has defined some great color schemes. These are well contrasted and look great on your screen. You can also define your own color schemes (to match corporate style). What more, you can even define schemes for fonts or combine both and create a new theme.
Use color schemes to change formatting quickly

3. Use cell styles

Consistency is an important aspect of formatting. By using cell styles, you can ensure that all similar information in your workbook is formatted in the same way. For example, you can color all input cells in orange color, all notes in light gray etc.
Apply consistent formatting with cell styles in Excel
To apply cell styles, just select all the cells you want to have same style and from Home ribbon, select the style you want (from styles area).
Learn how to use cell styles in Excel.

4. Use format painter

Format painter is a beautiful tool part of all Office programs. You can use this to copy formatting from one area to another. See below demo to understand how this works. You can locate format painter in the Home ribbon, top left.
Use format painter to format data quickly

5. Clear formats in a click

Sometimes, you just want to start with a clean slate. May be it is that colleague down the aisle who made an ugly mess of the quarterly budget spreadsheet. (Hey, its a good idea to tell him about Chandoo.org) So where would you start?
Clear formatting of a cell (or range) in a snap
Simple, just select all the cells, and go to Home > Clear > Clear Formats. And you will have only values left, so that you can format everything the way you want.

6. Formatting keyboard shortcuts

Formatting is an everyday activity. We do it while writing an email, making a workbook, preparing a report, putting together a deck of slides or drawing something. Even as I am writing this post, I am formatting it. So knowing a couple of formatting shortcuts can improve your productivity. I use these almost every time I work in Excel.
  • CTRL + 1: Opens format dialog for anything you have selected (cells, charts, drawing shapes etc.)
  • CTRL + B, I, U: To Bold, Italicize or Underline any given text.
  • ALT+Enter: While editing a cell, you can use this to add a new line. If you want a new line as part of formula outcome, use CHAR(10), and make sure you have enabled word-wrap.
  • ALT+EST: Used to paste formats. Works like format painter (#4)
  • CTRL+T: Applies table formatting to current region of cells
  • CTRL+5: To strike thru.
  • F4: Repeat last action. For example, you could apply bold formatting to a cell, select another and hit F4 to do the same.

7. Formatting options for print

What looks great on your screen might look messed up, if you do not set correct print options. That is why, make sure that you know how to use these print settings. All of these can be accessed from Page Layout ribbon. For more, you can also use print preview and then “page settings” button.
Formatting options for printing

8. Do not go overboard

Formatting your workbook is much like garnishing your food. No amount of plating & garnishing is going to make your food taste good. I personally spend 80% of time making the spreadsheet and 20% of time formatting it. By learning how to use various formatting features in Excel & relying on productive ideas like tables, cell styles, format painter & keyboard shortcuts, you can save a lot of time. Time you can use to make better, more awesome spreadsheets.

What are your favorite formatting tips?

Formatting (or making something look good) helps you get great first impression. I am always looking for ways to improve my formatting skills. While a great deal of formatting skill is art (and personal taste), there are several ground rules to follow as well. Applying ideas like consistency, alignment, simplicity and vibrancy goes a long way.
What formatting tips & ideas you follow? Please share them with us using comments.

Learn how to make better spreadsheets

Join Excel School & Make awesome Excel sheets

In my Excel School program, we focus not just on teaching Excel, but also teaching you how to make awesome Excel workbooks. You can see how I format my data, charts, dashboards & reports and learn hundreds of tips on formatting.
Even the lesson workbooks are beautifully formatted & packed with fresh ideas for you to try.
Consider joining our Excel School program, because you want to be awesome in Excel.
Posted by at 0 comments

What is one area of Excel you want to learn more? [Survey]

What is one area of Excel you want to learn more? [Survey]:
It is almost weekend. Today we (Jo and I) are going to watch a cricket match being played in Vizag. We are pretty excited as this is the first time we are watching a match in stadium.
So, let keep this light and fun. I want to know What is one area of Excel you want to learn more?
What areas of Excel you want to learn?


I will go first. I want to learn more about Data Tables & Simulation.
What about you? Go ahead and tell us using comments.
Note: Here are a few choices if do not know what else is out there.
  • Formulas
  • Array Formulas
  • Formatting
  • Conditional Formatting
  • Charting
  • Advanced Charting
  • Pivot Tables & Charts
  • Tables
  • Data Tables
  • Validation
  • Filters & Sorting
  • VBA (Macros)
  • Linking to Databases etc.
  • Solver
  • Statistical Analysis (regression, time series etc.)
  • Scenarios, What if analysis
  • Dashboards
So go ahead and tell us.
Posted by at 0 comments

Make VBA String Comparisons Case In-sensitive [Quick Tip]

Make VBA String Comparisons Case In-sensitive [Quick Tip]:
Today, while answering a reader’s email, I wrote this VBA code,
If Target.Value = "yes" Then

'do something

End If
But I realized that my code would run only the Target cell has “yes” in it. It wont run if the target cell has “YES”, or “Yes” or “YeS”.
This is because by default, all VBA comparisons are binary. That means, “yes” ≠ “Yes”.
One quick work-around for this problem is to use UCASE to convert target.value to Uppercase and then compare, like this,
If UCASE(Target.Value) = "YES" Then

'do something

End If
But this seemed painful, especially, if I had to do similar comparison at multiple places in my code, I had to use UCASE() everywhere.
If only there is an option to tell VBA how to compare?!?
Well, there is an option.

Use Option Compare Text

If you write Option Compare Text at the top of your module, all the VBA comparisons with in that module will use Text comparison instead of Binary comparison. Thus, “yes” will be equal to “YES”.

Do you use Option Compare?

There are 3 settings for Option Compare.
  1. Option Compare Binary: This is the default setting. Compares everything at binary level.
  2. Option Compare Text: Used for situations like this.
  3. Option Compare Database: Can be used only with MS Access VBA. Uses Database Table settings to determine how to compare.
This is the first time I have used Option Compare Text. But it seems like an elegant way to tell Excel VBA how to compare. I will be using it more often.
What about you? Do you use Option Compare? What are your favorite tips & tricks? Please share with us using comments.

More on Excel VBA

VBA (or Macros) is how you can tell Excel to automate parts of your work. It is a powerful programming language built right in to Excel (and other MS Office applications) to help you do more. If you are new to VBA, why don’t you go thru our Free crash course?
  1. Introduction to VBA & Excel Macros
  2. Understanding Variables, Conditions & Loops in VBA
  3. Using Cells, Ranges & Other Objects in your Macros
  4. Putting it all together – Your First VBA Application using Excel
  5. My Top 10 Tips for Mastering VBA & Excel Macros
Also, go thru our VBA (Macros) article collection for more tips, tutorials & ideas.
Posted by at 0 comments
Subscribe to: Posts (Atom)