Central Perk: MeasureCamp IV – Excel Tip Exchange

marți, 1 aprilie 2014

MeasureCamp IV – Excel Tip Exchange

Posted: 01 Apr 2014 01:00 AM PDT

Saturday was the fourth MeasureCamp – a free 'unconference' dedicated to web analytics. As it was my third time going I made a last-minute decision to lead a session of my own, to let people swap the Excel tips and tricks they'd accumulated over their careers. The lack of preparation was probably a mistake but hopefully it was useful anyway…

I don’t have the spreadsheet we were working on in the session: my main takeaway of the day was that you should bring a VGA to HDMI converter if you might want to use a projector with your new HDMI-and-USB-port-only laptop, rather than having to borrow someone else’s Mac.

So, instead, I’ve done my best to recreate the spreadsheet and the tips shared! Download it here.

Using awkward characters in strings in formulae

If you want to use a double quote in a string, use "".

Some functions, such as SUMIF, COUNTIF and VLOOKUP, treat the characters * and ? as wildcards – if you want to use * and ? to actually mean the characters rather than the wildcard, then use ~* and ~?. But this is only necessary in formulae that can use wildcards (and when using Find and Replace).

In the process of making the spreadsheet I've found that the tilde is more complicated: you use ~ for COUNTIF and SUMIF, but ~~ for VLOOKUP and SEARCH (and Find and Replace).

Use TEXT to get the day or month by name

The TEXT function will take in a number and give you back a piece of text in the specified format. The format “ddd” will give a shortened day of the week, “dddd” will give the full day name. Similarly “mmm” and “mmmm” will give the shortened and full name of the month.

If you have a number saved as text, then use VALUE to turn it into an actual number.

Use custom formatting to change the colour of numbers

Right click, go to Format Cells, and choose Custom. Then type in
[Color10] 0.0;[Red] -0.0;[Color15] 0
The first bit specifies how positive numbers are formatted (in this case, dark green and with one decimal point). The second is for negative numbers (in this case they appear red, with a minus sign and with one decimal point). The third is for values which are 0 (in this case they will appear grey and with no decimal points).

You can also make 0 values into something else.
[Color10] 0.0;[Red] -0.0;" - "
This will make 0 values appear ” – “. If you use them in formulae later Excel will still treat the value as zero, as you’ve changed the formatting rather than the value itself.

Shortcuts

If you’re using Windows, there are a few useful shortcuts with the formula keys:

F4 will repeat the last action.

F2 will let you edit formulae.

If you select part of a formula and press F9, the selected part will be replaced by the value of that bit. For example, if you have a formula:
=1+1=4
If you selected 1+1 and pressed F9, it would change it into 2.

Having Fewer Columns

If you only want seven columns to appear, then select the eighth column and press Ctrl, Shift and Right Arrow (or Command, Shift and Right Arrow on a Mac) to select all columns to the right. Then right click and select Hide.

Pivot Tables

To make a pivot table:

Select your data. (Make sure all columns have headers!)

Go to the ‘Insert’ tab in Windows, or the Data tab on a Mac. Click PivotTable.

If you're on Windows there’ll be a menu: just click 'OK'. You'll then have a Pivot Table in a new worksheet. You can then choose the fields you want to show. You can also drag and drop the field names to rearrange them.

If something has come out as 'Count of blah' rather than 'Sum of blah', then right click on it and go to 'Value Field Settings'. Then you can change it into a Sum.

You can add calculate fields if you want something like ROI that can't just be summed. Click on 'Fields, Items & Sets' in the Analyze tab and then on 'Calculated Field…'

You can then enter in a name for the new field, and the formula for the field.

You can also group values, for example if you have data by the day and want to see it summarised by week or month. Right click the column you want to group, then click 'Group'. In this example I've grouped areas with 1 or 2 staff, and those with over 2.

But beware that this may mess up calculated fields that use the grouped field!

Counting Unique Values

There was a question on how to count the number of unique values in a field. For example, if you have a list of user IDs and purchases, and want to know how many users there are – you can’t just count the number of cells with values in, as the user IDs will be duplicated if someone made a repeat purchase. You can’t use COUNTIF as that will count the number of times an ID appears, rather than how many unique IDs there are.

However, there is a formula specifically designed to do this if you download the add-in PowerPivot.

After some post-conference Googling I've found a complicated solution using an array formula. Also, StackOverflow suggests =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")) but this will only work with numeric values.

Alternatively, you could copy the user ID list, remove duplicates from that copied list, and see how many IDs you're left with.

The JOIN Function in Google Docs

A non-Excel tip! If you want to concatenate a bunch of text with a character in-between – for example, if you’ve got a list of words and want to make a regular expression like word1|word2|word3 by sticking them all together with |s in-between – then copy the list of words into a Google Doc spreadsheet and use the JOIN function.

I've made an example spreadsheet here.

Want more?

If you’d like more Excel tips I’d recommend:

Russel McAthy’s 'Why Do You Hate Excel?' MeasureFest slideshare
PPCHero, which has more Excel articles than I can usefully list
Everyone loves Chandoo.org!
And, if you don't mind the self-promotion, I've written posts on basic Excel hints, array formulae, and learning macros.

Thanks

Thanks to everyone who came along and took part!

Please get in touch, either by Twitter or in the comment section, if I’ve missed out any of the tips, or if you just want to add something entirely new.

The post MeasureCamp IV – Excel Tip Exchange appeared first on White Noise.