How To Be Great at Excel

You might think that this point will be about hard work and determination. It’s not. You might think it’s about acquiring the most knowledge. You’d be wrong. I don’t care if you’re the hardest working person in your cube farm and you have Bill Jelen in a hole in your basement. There are two things you need if you want to go from average to great.

Keyboard Centrism

Many of you are groaning even though you saw this coming a mile away. But hear me out. Even the Excel masters who aren’t as keyboard-crazy as me still use their keyboard more than the average user. To be great at Excel, or anything else, you have to use it. That means practice. That means making mistakes. That means fixing those mistakes and trying again.

Terrible analogy alert: There are two people who wish to be great at breaking up a rack of billiards balls, so they practice. The first person can re-rack the balls in ten seconds. The second person takes one minute. I can’t predict which person will end up better, but the first person will have a greater opportunity because he will have broken more racks per hour of practice.

Keeping your hands on the keys rather than in the air between the keyboard and mouse won’t make you great at Excel. But it will give you more iterations using any piece of software you want to learn (except Minesweeper).

Move your mouse to the left side of your keyboard for two hours, one day per week. You’ll learn more keyboard shortcuts in those two hours our of sheer frustration than you would in a whole day of trying to remember them.

Data Organization

Many things in Excel go from impossible to simple by changing how the data is organized. If you can’t quite get that pivot table to do what you want, it’s probably because you’re data isn’t laid out well. If that chart isn’t doing what you want, you probably need to restructure the underlying data. Go read peltiertech.com and see how my charting solutions involve setting up the data first. The answer is almost never “click the waterfall button”; the answer is usually organizing your data so it works with how charting features work.

You don’t need any formal training to learn how to organize your data. In fact, to get Excel to do what you want, sometimes you have to break the rules of data organization. However, I encourage anyone who wishes to excel at Excel to learn about databases. Learn what a record is. And a field. Learn the first three normal forms. Or you could buy Microsoft Access 2013 Bible, a little page turner that Mike Alexander and I wrote.


Microsoft Access 2013 Bible

I imagine you have an opinion on how to be great at Excel. That’s the what the comments are for, so have at it.

Analyze ‘free’ money

Recently, I received yet another promotion from yet another company offering me money at zero percent interest with the predictable asterisk next to the zero percent. Instead of just shredding the offer I decided to create a downloadable Excel template to analyze the offer, which was an interest-free 18 month loan for a 4% transaction fee with a minimum $10 fee.

Obviously, the transaction fee makes sure that the money is not ‘free.’ So, how does one calculate the cost of the loan? I settled on an “effective interest rate.”

image001

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0920%20free%20money.shtml

Tushar Mehta

The Excel FORMULATEXT function

With Excel 2013, Microsoft introduced several new functions, one of which is worth a separate mention. FORMULATEXT displays as text the formula in a cell.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0116%20formulatext%20function.shtml

Tushar Mehta

Excel Advanced Filter

Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom filter and Advanced Filter provides that capability. It is a data-driven mechanism that uses Excel formulas to extract specific information from the original data. For those who may have heard of SQL but have never been motivated to learn it, you can now leverage some of the power of SQL without learning a single word of SQL!

The layout of this document is as follows: 1) Introduction to the data set used in the examples, 2) Introduction to the Advanced Filter dialog box, 3) Filter using column headers, 4) Filter using Excel formulas, 5) Extract unique data, 6) Work with dynamic source data, and 7) Create a filter in a different worksheet or workbook.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

Tushar Mehta

Unicode and diacritic characters

The primary role of Excel is analysis and visualization of data, which put less emphasis on the use of special text characters. Nonetheless, there will always be some need for special characters, both Unicode characters and diacritics in Excel.

A diacritic in English is a glyph that modifies the sound of the character to which it is attached. Examples are naïve, résumé, and saké. In other fields, glyphs modify a letter to convey a specific meaning. Examples include:

· In Statistics the sample mean is denoted by x-bar (x̄) and the sample proportion by p-hat (p̂). Examples of Unicode characters are the population mean (the lowercase Greek letter mu, μ) and the population standard deviation (the lower case Greek letter sigma, σ).
· In Economics, profit is denoted by the Greek letter pi (π).
· In Mathematics, well, in Mathematics, there are a plethora of symbols including the capital sigma (Σ) for sum and ∫ for an integral.
· Currencies are denoted by symbols such as the US Dollar ($), the Euro (€), the Japanese Yen (¥), the Chinese Yuan (元), and the Indian Rupee (₹).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0123%20Unicode%20and%20diacritics.shtml

Tushar Mehta

Freeze Panes and Split Panes

When working with a worksheet that contains a large number of rows and/or a large number of columns with row and column headers, it is very helpful to always view the headers no matter where one scrolls through the document.

An example is shown below. The table lists the year-by-year carbon dioxide emissions by country (the data set comes from data download page at Gapminder – http://www.gapminder.org/data/). The current worksheet view shows data from the 1950s (columns GQ through GZ) and countries that are alphabetically towards the end of the list of countries (rows 235 through 259). At the same time, the column headers (row 1) and the row headers (column A) are still visible. This lets one quickly establish a context for the numbers. This is the result of using Excel’s Freeze Panes feature.

A complementary feature is called Split Panes. The key differences between freeze pane and split pane are the somewhat different behavior while scrolling and an easier way to adjust the panes, which, of course, comes with extra responsibility to manage the risk of accidental changes to the split panes configuration.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0201%20split%20freeze%20pane.shtml

Tushar Mehta

Installation Instructions for Office add-ins

I updated the installation instructions for Office VBA add-ins to include Office 2010, Office 2007, and Office 2003. The updated material is at http://www.tushar-mehta.com/excel/software/installation/index.htm.

Tushar Mehta

Create a header cell with text separated by a diagonal line

In creating a table header that explains what the row and column values represent, a common approach is to use a cell with text separated by a diagonal line. This, first for me, video tutorial explains how.

For those who prefer a text explanation:

Format the cell “border” to add a diagonal from the left-top to the right-bottom (it’s one of the line choices in the Format Cells dialog box | Borders tab).

Then, type several spaces the literal Hours ALT+ENTER to create a newline in the text and then the literal Days.

Adjust the number of spaces before the literal Hours to get the desired effect.

An alternative, which is simple albeit crude, is to type Days space \ space Hours. {grin}

Tushar Mehta