Mount ISO Images in Windows 8

Well, it took a long ass time but Windows users can finally do what Linux and Mac nerds have been able to do… for about as long as I can remember: mount ISO images without third-party tools.

That’s right. Mounting ISO images in Windows 8 is as simple as right-clicking the ISO image file, then clicking Mount.

ISO images are commonly used when downloading programs that you would typically install via CD or DVD. For example, after recently installing Windows 8, I downloaded the Office 2010 ISO image from Microsoft. Normally I’d have to do one of two things to this file before I could actually use it to install Office on Windows 7 and any earlier versions:

  1. Use a third-party tool to mount the ISO image, which then makes all the files it contains available for use.
  2. Burn the ISO image to a DVD and install it like it’s 1995.

Since downloading the correct third-party tools is a hassle, and since 1995 was 17 years ago, you can imagine how happy I was to see this when I right clicked on the ISO image file:

Windows 8: Right Click on ISO Image to Mount
New to Windows 8: right click ISO image files to mount.

As you can see, after clicking Mount Windows assigned the next available drive letter (in my case, E:) and switched to the file browser. All that was left to do was double-click setup and install Office.

Mounted ISO Image
Once mounted, Windows allows you to browse through the files contained within the ISO image.

I like it.

How to Restart, Shut Down & Sleep in Windows 8

Microsoft’s been pretty busy lately, releasing a substantially redesigned Windows 8 just last week and putting the final touches on Office 2013 to be released in Q1 2013.

I just installed Windows 8 a few hours ago, so this first post will address something which has already given many people heartburn: how the hell do we restart this thing? Or shut it down, for that matter?

Good news, it’s still easy to do.

  1. Press the Windows Key + C (Win + C) to pop open the new sidebar.*

    Windows 8 Sidebar
    Press Win + C to activate the Windows 8 sidebar.
  2. Click Settings → Power

    Windows 8 Settings > Power
    After pressing Win + C, click Settings > Power to see sleep, shut down and restart options.
  3. Click either Sleep, Shut down or Restart.

End scene.

* Note: you can also point your cursor in the upper right corner of your screen to activate the sidebar, but I prefer using keyboard shortcuts instead.

Ditch the Mouse: Quickly Select Excel Ranges With Your Keyboard

Mouse: Just Say NO
I may have made this in Paint.

I’m going to write a lot about ditching the mouse in favor of keyboard shortcuts, so consider this one of many to come. And while we’re at it, get used to me spamming this next sentence throughout my shortcut posts: Using keyboard commands over hunting and clicking with the mouse will save you time. Period.

Next time you open Excel to manage a list, a table with headers or just giant blocks of data, please, please avoid using your mouse to select or highlight cells and cell ranges. Use these keyboard shortcuts instead:

  • Ctrl + Shift + Arrow Key: I consider this among the most useful Excel keyboard shortcuts around. Extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
  • Ctrl + Arrow Key: Jumps to the edge of the current data region in a worksheet.
  • Ctrl + Home: Jumps to cell A1 no matter where you are on a worksheet.
  • Ctrl + End: Jumps to the right bottommost nonblank cell.
  • Shift + Arrow Key: Extends the selection of cells by one cell.

Out of these five navigation shortcuts I probably use the first and second 95% of the time. The first shortcut, Ctrl + Shift + Arrow, to select large blocks of data like a long list or a table. This one is especially useful if you have a table or any list with more than one column because you can press Ctrl + Shift + ↓ to select down the entire first column, then while keeping Ctrl + Shift pressed, hit  to quickly select the remaining columns to the right.

Some of the data feeds I work with regularly generate tables 30+ columns wide and often thousands of rows long, so being able to exactly select the entire table by simply pressing Ctrl + Shift + ↓ + → saves me a lot of time and hassle. The second shortcut, Ctrl + Arrow Key, is how I jump around in Excel to get from one end of my data to the other.

It’s super, so do it.

Excel 2013 Flash Fill

Flash Fill Create Names from Email Addresses

I’ve been playing around with the Excel 2013 preview, and damn. It’s nice.

Excel newbs and ninjas alike will find Flash Fill – one feature new to Excel 2013 – intuitive, powerful and super convenient. Flash Fill takes the grunt work out of list-making and data manipulation, relying on clever algorithms to recognize patterns and complete your lists for you, formula-free. That’s right, sans formula. And since list-making and data manipulation are two popular reasons many people use Excel to begin with, this will definitely be a hit.

Data Manipulation? What?

When I refer to data manipulation in this post I’m talking about any instance where you use Excel to change or modify the way your data looks. More often than not the data exists in lists too large for manual editing. Here are some examples to illustrate situations where you might use Flash Fill:

  • Converting phone numbers from XXXXXXXXXX to (XXX) XXX-XXXX
  • Formatting social security numbers to XXX-XX-XXXX
  • Removing incorrect or extra spaces from text
  • Formatting names in many ways (like Doherty, Ryan J.)
  • Converting dates from YYYYMMDD to M/D/YYYY
  • Inserting the leading zero back into Postal Codes (like 04101)

Flash Fill in Action

Let’s say you have a list of email addresses in the format firstname.lastname@fake-email.com, and you want to quickly create a Name column from that list.

Flash Fill Create Names from Email Addresses
Flash Fill recognized what I was trying to do and did the rest.

In Excel 2010 and earlier, this simple task would require using either Text to Columns, or some combination of the LEFT, FIND and CONCATENATE functions. It would likely need multiple steps. Or manual data entry, and nobody wants that.

In Excel 2013, I created the Name column and just started typing the first and last name in cell B5, like so: Jerry Peters. When I hit Enter to move down to cell B6 and typed “N,” which is simply the first letter of the next persons first name, Flash Fill did the rest. In the screen shot above, the ghost-like list from cell B7 down shows Flash Fill’s suggestion. You can go the other way, too (create email addresses from Name columns).

Flash Fill Create Email Addresses from Names
Once Flash Fill recognizes your pattern it shows a live preview.

Phone Numbers

Flash Fill Phone Numbers
I chose to use (XXX) XXX-XXXX, but could have used XXX.XXX.XXXX as well.

Start typing your data in the format you prefer. In the phone number example, I typed “(555) 123-4567” into cell B5. When I hit Enter and typed “(” into B6, Excel recognized that format and Flash Fill suggested how to finish the list for me. If I had typed “555.123.4567” into cell B5, Flash Fill would have suggested completing my list in that format instead (XXX.XXX.XXXX). (Note: since we’re using numbers mixed with text, Excel treats it all as text instead of numbers, so you will automatically get suggestions).

Flash Fill Treats Numbers as Text

My note above brings up a good point. Flash Fill already handles text like a pro, but definitely leaves room for improvement when it comes to numbers. Our friends from the Excel team sum it up best:

The algorithm engine we’re using looks at everything as text. So it doesn’t know any special rules that go along with numbers (for example, it’s OK to drop leading zeros when dealing with numbers, but not so much when you’re dealing with Postal Codes or other IDs).

So when it comes to using Flash Fill for dates, zip codes or other numeric-only data, we’ll still need a few extra steps to get what we want (like first formatting the target column as TEXT). Either way, it’s still a step forward.

Some Flash Fill Tips

  • To convert lowercase values to UPPERCASE, just start typing in UPPERCASE.
  • To remove extra spaces from values like addresses, just start typing the addresses without spaces.
  • The Flash Fill algorithm for pattern recognition treats numbers and digits the same as text. When working with numbers only, always format the target column as TEXT.
  • To get automatic suggestions:
    • You have to edit right next to related data (i.e. the next column over from your source data with no blank columns between).
    • You have to make two sequential edits, one right after the other.

My experience using Flash Fill so far has been mostly positive, with a few quirks now and then when it doesn’t work quite how I though it would. Come across a pattern that doesn’t work well with Flash Fill? Or think of a unique way to make use of this new feature? Let me know!

Data Cleaning with Flash Fill

Compare Two or More Lists in Excel with Conditional Formatting

I’ve recently posted two easy ways to compare two lists in Excel. One uses ISNA and MATCH and one IF and COUNTIF. I like both of these methods because they allow you to:

  1. Learn how to use and modify four powerful Excel functions
  2. Quickly apply AutoFilter to view just the missing values
  3. Easily manage and compare massive lists of data

For shorter lists, however, there is a much easier, non-formula way to compare two lists in Excel 2007 and later.

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Highlight both lists.

    Highlight the entire range of data.
    Highlight the entire range of data.
  3. Navigate to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values…
  4. In the Duplicate Values dialog box, select “Duplicate” from the drop-down list to highlight values (in our example, names) found on both lists and click OK.

    Apply conditional formatting to highlight duplicate values.
    Apply conditional formatting to highlight duplicate values.

It doesn’t get much easier than that. If you’d rather Excel highlight the values missing from either list, select “Unique” from the drop-down list on the Duplicate Values dialog box (step 4 above).

Apply conditional formatting to highlight unique values.
Apply conditional formatting to highlight unique values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with Conditional Formatting

Compare Two or More Lists in Excel with IF and COUNTIF

As I previously discussed, Excel provides many useful ways to automatically compare two lists of data or information. In our other example we compared two lists of four digit account values; for this example we’ll compare two lists of names.

You and a friend are throwing an epic party and have each maintained separate guest lists to track who’s coming. After a few weeks you decide to compare both lists to make sure everyone on your friend’s list (List B) is also on your list (List A).

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Create a third column in column D called List C (leave column C blank for easier readability).
  3. In cell D5 enter the formula: =IF(COUNTIF(B:B,A5),A5,0) and press Enter.

    Enter formula into cell D5
    Enter formula into cell D5.
  4. Select cell D5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Equal To…
  5. In the Equal To dialog box, type 0 and click OK.
  6. Select cell D5 down to the end of the lists and press Ctrl+D to copy the formula and conditional formatting down.

    Copy the formula down to the end of the longer list (List B in this example).
    Copy the formula down to the end of the lists.

The Result

A name in List C means the corresponding name from List A was also found within List B. In the screenshot above, cell D5 displays the “Moon Barrientos,” which means that name was found on both List A and B. On the other hand, cell D12 displays Red Zero which means the corresponding name from List A (in this case, “Aracely Rock”) does not exist within List B.

This example demonstrates how to determine if a particular value – in this case a name – in List A also exists within List B. To find the opposite – whether a name in List B also exists within List A – simply change the formula in cell D5 to: =IF(COUNTIF(A:A,B5),B5,0) and press Enter. Then copy that formula down to the end of the lists.

How & Why it Works

Our formula makes use of two functions, IF and COUNTIF. The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. COUNTIF counts the number of cells within a range that meet the given condition.

The Formula

=IF(COUNTIF(B:B,A5),A5,0)

COUNTIF has two required arguments, or inputs, to work:

  • range (where should Excel look, B:B in our example)
  • criteria (what should Excel find, A5 in our example)

COUNTIF checks column B (using B:B checks the entire column) for the name in cell A5. If it finds the name (i.e. the criteria you specified was met) it returns 1, otherwise it returns 0. Note: when COUNTIF returns 1 or 0, Excel treats that as TRUE (1) or FALSE (0).

The IF function has three required arguments, or inputs, to work:

  • logical_test (any value or expression that can be evaluated to TRUE or FALSE)
  • [value_if_true] (what value should Excel return if if logical_test is TRUE)
  • [value_if_false] (what value should Excel return if logical_test is FALSE)

In our example COUNTIF is the logical_test that gives the IF function either a 1 or 0, depending on whether it finds the name we specified from cell A5 within column B. If it finds the name (i.e. the logical_test is TRUE), we specified A5 as the [value_if_true] so Excel will display that name and move on. If it does not find the name (i.e. the logical_test is FALSE), we specified 0 as the [value_if_false], so Excel will display 0 and move on.

You can quickly see a list of names missing from List B by filtering List C to show only “0.” The conditional formatting just makes it easier to spot the missing values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with IF and COUNTIF

Compare Two or More Lists in Excel with ISNA and MATCH

People compare lists (or columns) of information all the time, and Excel provides several easy ways to it. In fact Microsoft sums up several real life examples quite nicely:

For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other. Which new clients need to be added to the master database? Which credit card receipts are not reflected on the monthly statement, and vice versa? What checks and deposits are still outstanding? It’s a tedious task if you do it manually.

In the example below we have two large lists of four digit account values: List A and List B. We want to know if any account values from List A are missing from List B, and vice versa. To do this we’ll write a formula using the ISNA and MATCH functions.

How to Do It

  1. Arrange the lists in columns, leaving at least two blank column in between. I chose column A for List A and column D for List B.
  2. Name the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”
  3. In cell B5 enter the formula: =ISNA(MATCH(A5,D:D,0)) and press Enter.
    Enter formula into B5
  4. Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…
  5. In the Text That Contains dialog box, type TRUE and click OK.
  6. Select cell B5 down to the end of List A and press Ctrl+D to copy the formula and conditional formatting down the entire List A.
  7. In cell E5, enter the formula: =ISNA(MATCH(D5,A:A,0)) and press Enter.
  8. Repeat steps 4-6 for cell E5, making sure to copy the formula down the entire List B.
    Copy formulas down both lists

The Result

If any value from List A is missing from List B, a red TRUE will appear next to that value in List A’s “Missing?” column. If any value from List A is found in List B, FALSE will appear next to that value in List A’s “Missing?” column.

A TRUE = value is missing from the other list
A FALSE = value is not missing from the other list

You could take it one step farther by filtering either list to display only TRUE which would then only display the values missing from either list.

Done and done.

How and Why It Works

ISNA is the first function in our formula, which does only one thing in life: checks whether a value is #N/A (the error value Excel uses to say “no value is available”). If a value is #N/A in an ISNA formula, it’ll display TRUE. Otherwise it’ll display FALSE.

MATCH is the second function, which returns the relative position of an item in an array (in our example, either List A or List B) that matches a specified value in a specified order. In our example we are using MATCH to tell ISNA whether to display TRUE or FALSE.

If MATCH finds a value from List A within List B, it’ll return the relative position of the value in List B. Whenever MATCH returns the relative position and not #N/A, ISNA simply spits out FALSE. If MATCH does not find a value from List A within List B, it’ll return #N/A and ISNA will display TRUE.

The conditional formatting just makes it easier to spot the missing values.

The Formula

=ISNA(MATCH(what,where,type))

MATCH has three required arguments, or inputs, to work:

  • lookup_value (what do you want Excel to lookup or find)
  • lookup_array (where should Excel look. In our example, A:A and D:D selects those entire columns)
  • match_type (what type of a match Excel should find: the next largest, an exact match only, or the next smallest)

ISNA literally checks whether the cell value “IS #N/A,” or “IS NO VALUE AVAILABLE?”

  • If it IS #N/A, it’ll display TRUE.
  • If it IS NOT #N/A, it’ll display FALSE.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with ISNA and MATCH