Monthly Report Tutorial

As posted on my blog yesterday.

At a former client, I was asked to submit monthly reports that show details of work performed in 15 minute increments.

My line of thought went something like this,

“Let’s see, a monthly calendar, something like the one on my fridge door comes to mind and making one in Excel should be easy…”

One problem is space. If I do several tasks in one day, do I use tiny font to make the details fit, or do I make the calendar larger to the point that I have to scroll copiously?

Also, just how practical is that style of calendar going to be when it comes to adding up total time per task? Something along the lines of a regular timesheet would be better.

I can easily fit 32 rows on my laptop screen. That’s a good start. So here’s how to do the same thing I did, if you are interested.

Leave the first row for your headers. In cells A1 and B1, enter “Date” and “Day”, then change the orientation. Right click the cells, select Format Cells, Alignment, and change Orientation to 90 degrees.

(You might want to change the Alignment too. Choose from the options on the Alignment Group on the Home Tab)

Enter the first day of the month in cell A2. Select range A2:A32, then change the format to either “d/m” or “m/d” as you prefer. Right click the cells, select Format Cells, Number, and enter the format in the Type text box in the Custom section.

Now enter =A2+1 into Range A3:A32 and click your Ctrl and Enter keys simultaneously to enter the formula into all selected cells.

In the same way, enter =CHOOSE(WEEKDAY(A2,1),"Su","Mo","Tu","We","Th","Fr","Sa") into Range B2:B32.

Adjust the width of both of these columns and set the alignment to suit.

You should have something like this.

And now for the details. Long descriptions take up space, so let’s use numbers instead. Keep in mind that longer tasks won’t be completed in 15 minutes, and recurring tasks will be duplicated so that’s going to cut down the number of tasks in total. With any luck, we can keep things within double digits.

Start times allotted for the 15 minute intervals go in Row 1. Adjust the Orientation to 90 degrees. “h:mm” is a suitable format.

The task descriptions that match the numbers can go on the right. But note the numbers to their left to perform a lookup.

Important: adjust the following ranges to suit your requirements. Use Named Ranges if you prefer.

Enter formulas to add up the time. Type the following formula into Cell AI2, and drag down to the end of your list.

=IF(COUNTIF($C$2:$AE$32,AG2)=0,"",COUNTIF($C$2:$AE$32,AG2)/4)

You should have something like this.

You can freeze the first row if the number of tasks exceed the number of visible rows on your screen. (View Tab, Windows Group, Freeze Panes, Freeze Top Row)

Now for some extra features to enhance visibility. Why not add some Conditional Formatting to highlight the weekends? With Range A2:AE32 selected, click the Home Tab, Styles, Conditional Formatting, New Rule, then “Use a formula to determine which cells to format” and enter this formula. (Click the Format button to choose a suitable format)

Here’s the result.

An ActiveX Combo Box and a bit more Conditional Formatting makes it easy to see when the work was done. If you can’t see the Developer Tab on the Ribbon, select the File Tab, Options, Customize Ribbon, then tick “Developer” on the list to the right and click the OK button.

On the Developer Tab, select Insert from the Control Group to add an Active X Combo Box. (I’ve already added one to Cell AH1)

Right click the Combo Box and select Properties. Set the LinkedCell and ListFillRange properties. I’ve hard-coded my ListFillRange range reference but you can use Named Ranges too, as in “=Tasks” without the quotation marks.

When finished, toggle off Design Mode on the Developer Tab.

Note the linked cell. That gives me the selected item of the list. Now I use another formula to get the reference number which I have put in the cell below the linked cell (In this case, Cell AJ3).

=MATCH(AJ2,AH:AH,0)-1

If I select the first item on the Combo Box, Cell AJ3 will show 1.

Here’s the Conditional Formatting for the details part of the report. (Range C2:AI32)

And here’s the Conditional Formatting for the list. (Range AG2:AH32)

I also added some Data Bars to the hours.

And we’re done.

No VBA was used so you can send the file without explaining the need to enable macros.

Here’s a download link if you want to skip making one yourself.

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 even the best emergency loans? 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

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