Excel Shortcut Keys – Part II

In my previous post about Excel shortcut keys I’ve showed you some of the most basic shortcuts that you can use on Excel. There are many more shortcut keys in Excel and the best way for you to memorize all of them is to have printed Quick Reference Cards next to your computer to check anytime you need.
Here you can find a good and complete set of this Quick Reference Cards for Excel 2010:

Keyboard shortcuts - Ctrl keys

Keyboard shortcuts - Function keys

Keyboard shortcuts – Miscellaneous

Custom Lists

Excel custom lists can be used to fill out cells or to sort data in a user-defined order. Excel comes with some pre-defined custom list that can be viewed by going to the File-Options-Advanced menu. Scroll down until you find the Edit Custom Lists button.

CustomLists1

When you click the button, a new dialog window will open where you can see the pre-defined custom lists:

CustomLists2

This custom list can be anything from the months, to colors, brands, etc. To create a new custom list, just select the NEW LIST on the “Custom lists” field and start typing the entries on the “List entries” window, pressing Enter on each entry to move to the next line of the list. When you’re finished, press the Add button and your new list will be created and will appear on the “Custom lists” window, like on this example, where I’ve created a colors custom list:

CustomLists3

You can also also write on a range of cells the values that you want to use to create your custom list and use the “Import list from cells” option where you specify the range of cells that have your data, click on the Import button and your custom list will appear on the “Custom lists” window, like on this example:

CustomLists4

Now, let’s see how to use this custom lists. For example, on cell A1, type “Jan”. Then select the cell and drag to the right, by pushing the right bottom little black square that you see on the cell to see what happens.

CustomLists5

You will get something like this, depending how far did you pushed the cell to the right:

CustomLists6

You can use this method to fill out cells with data from other custom lists that you have on your Excel. You don’t need to start with the first value on your custom list. For instance, instead of inserting “Jan” on cell A1, if I insert “Mar” and push the cell to the right, I would get a list of “Mar, Apr, May…”.

You can use your Custom List to sort data using the order that you defined when you created the list. For instance, I’ve this table of data:

CustomLists7

I want to sort the columns by months. I already have a Custom List build with the correct order (“Jan”, “Feb”, “Mar”…) that I can use. Just select the range A1:C2, go to the Data-Sort option on the menu bar and when the Sort dialog box opens, on Row, select “Row 1”, because we want to sort by this row, and on Order select “Custom List…”. This will open the Custom Lists dialog where you need to choose the right custom list to use.

CustomLists8

Click OK on both dialog boxed and you should get you table sorted on the correct order: “Jan”, “Feb” and “Apr”.

Avoid Merged cells in Excel

This can be a surprise to you, but it’s true! You should avoid using merged cells in a Excel worksheet because it limits it’s future use. Here’s an example. This is how my table originally looks:

Merged_Cells1

My table title is not centered across the table. I can simple select cells B2:C2 and click on the Merge & Center on my Home tab. I can also align “Missed both tests” across C2:D2. My table would look like this:

Merged_Cells2

Definitely better, in terms of look, but if, for instance, you want to sort the test results by the results of Test 1 column, by selecting the range B3:D7 and going to Data-Sort and try to sort by Test 1 column, you will get an error message, like this:

Merged_Cells3

Let’s get back to our original table, without merged cells. To get the same look result, you should consider using “Center Across Selection” instead. Please follow this steps:

1 - Select the cells that you want to “merge”, in this case, cells C2:D2;
2 – Open the Format Cells dialog box (Home Ribbon, Font group, little button in bottom right or right click and select Format Cells from the list) 
3 – On the Format Cells dialog box, go to the Alignment tab and on the “Text alignment”-Horizontal, select the “Center Across Selection” option and click OK, like this:

Merged_Cells4

4 – Select cells C6:D6 and repeat the process.

Your table will look like the one with merged cells but now, let’s try to do the sort again by the results of Test 1 column, like you tried before. You don’t get any error now, do you?

This is to show to you that you can get the same look on your worksheets using merged cells and Center Across Selection methods but if you need to use your data to further operations, like sorting, using merged cells, you will be restricted. With Center Across Selection you will not have this kind of problems.

Excel Mashups

This is for advanced users, more for web developers that want to create spreadsheet-based mashups. A mashup is just a web page that takes data from existing sources and combines it into something new.
You create an Excel mashup by uploading a workbook to SkyDrive and then embedding it on a web page. Then, you use JavaScript to programmatically interact with that workbook.
I’m not going to explain here how to do this but just point you to the new site from the Excel team (http://www.excelmashup.com/) where you can learn all about it.

Sum every x Row

This can be very handy. Assume that you have your data on cells A1:A12 and you want to sum only, say, the values on each 3rd row. Using a SUMPRODUCT() formula like this:

=SUMPRODUCT(--(MOD(ROW(A1:A100),3)=0),A1:A100)

you can sum every 3rd row. You can adjust the 0 like this:

0 will sum every 3rd row
1 will sum every 3rd row starting with row 1
2 will sum every 3rd row starting with row 2

You can also adjust the 3 to change the number of rows to be summed. You can see here an example on the use of the multiple variations of this formula:

SUM_x_rows