uCertify–Online Version

I’ve tested the new online version of the uCertify PrepKits that I’ve used before and that I’ve reviewed here. This new online platform means that you don’t need to install anything on your computer to start using the prep kits.

Some of the features of Online PrepEngine are:

  • Complete location and device independence: Available online 24x7 or go mobile (supported on all major Smartphones and tablets)
  • Digital book integrated with images, videos, audio, quizzes, flash cards, assessments, assignments, study notes and much more
  • Powerful Analytics to track and gauge learning effectiveness for students, teachers and school administrators
  • Self-Study or Teacher/Mentor assisted learning
  • Student-Student & Student-Teacher Collaboration tools
  • Customizable roles and access for students, TAs, teachers and administrators
  • Simple, yet powerful teacher portals to customize courses, pre-assessments, tests and assignments
  • Hassle free, highly available and scalable hosted solution


Additionally, there is another feature ‘Course Advisor’ which is a helpful tool for one who is confused to choose certification exam. This tool will help such person to choose certification based on his/her interested technology, experience level, and education.

As before, I found that this tool is extremely simple to use. I just logged in and went straight to the Courses area where I found the courses that I had available. I open the 77-888 (Microsoft Excel 2010 Expert) and I had many options to follow, like a “Pre-assessment”, where I had 15 question to test and assess my strengths & weaknesses, a “Practice” mode with 74 questions, a “Quiz” with 101 questions, “Flash Cards” with 89 flashcards as well as, among others, a “Final Test” to test your knowledge and make you a final test to see if you could pass the official exams.

The online platform has a good design and it’s very intuitive to use. You can find hundreds of courses and certifications here. The value of the kits remains very good and I think that it’s a good value for money if you’re thinking on going for any IT certification, I’m sure that you will find here the prep kit to help you train and test your knowledge

Lookup with multiple criteria's

This article was originally written for the Microsoft Excel Blog. The purpose of this article is to show Excel users some technique’s, and Excel functions, that they can use to lookup up a value on a table, using more than one criteria. If you have only one criteria, it’s simple, you can use a plain VLOOKUP formula to do that. If you want to use more than one, then there are lots of ways of doing it, using several of Excel’s functions like VLOOKUP, LOOKUP, MATCH, INDEX, etc.

Let’s see a scenario where we want to use two criteria’s to return a value from a table. This is the data table that we have:

image

As you can see, we want to use a “Name” and “Product” criteria to return a “Qty” value on cell C18. We are going to look for the Name “James Atkinson” and the Product “Milk Pack” to return the Qty from the table. Because the value that we want to return is a number, we can use a simple SUMPRODUCT() formula in C18 cell, like this:

=SUMPRODUCT((B3:B13=C16)*(C3:C13=C17)*(D3:D13))

What this does is look on the range B3:B13 for the value on cell C16 and on the range C3:C13 for the value on cell C17 and where it finds both, return the value on column D, from the same row where it found both criteria’s. Here’s how it will look:

image

It is returning the value 1 that corresponds to the value on cell D4 because it found “James Atkinson” Name on row 4 and also “Milk Pack” on the same row, thus returning the value of column D from that row. Let’s just change value on cell C5 from “Wine Bottle” to “Milk Pack” to see what happens with the formula on cell C18:

image

Because our formula found two lines where both criteria’s were found, it is summing the values on column D on rows where they were found, giving us a Qty of 6.

This technique cannot be used is we wanted to look for two criteria’s and return a text result. For instance, this, would not work:

image

We are looking for the Name “James Atkinson”, where the Qty is 1 and we want to return the Product name that matches this two criteria’s. This formula would give us a #VALUE error! Instead we could use a formula using a combination of SUMPRODUCT, INDEX and ROW functions, like this:

=INDEX(C3:C13,SUMPRODUCT((B3:B13=C16)*(D3:D13=C18)*ROW(C3:C13)),0)

We use the SUMPRODUCT function to find out the row where both criteria’s are found and return the corresponding row number using the ROW function. Then we use it on the INDEX function to return the value on the array C3:C13 that is on the row number provided. The result will be like this:

image

This could also be done using a different technique. You could use this formula on cell C17:

=LOOKUP(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13))

The result will be the same as the previous solution. What this formula does, is divide 1 by an array of True/False values (B3:B13=C16) and then by another array of True/False (D3:D13=C18). This will return 1 or a #DIV/0 error. Using 2 as the lookup value, then the match will be with the last numeric value in the range, i.e. the last row where both conditions are True. This will be our "vector form" of the LOOKUP, so then you get the corresponding value returned from C3:C13. I used 2 as the LOOKUP value but it can be any number, starting at 1. If the formulas don’t find any match, of course you will get a #N/A error!

You could also use an array formula, using the MATCH function, like this:

{=INDEX(C3:C13,MATCH(1,(B3:B13=C16)*(D3:D13=C18),0))}

On this technique, I used the MATCH function to find the row where both conditions are found. This will return a value of 1 that will be matched to the 1 that was used as the lookup value of the MATCH function, thus returning us the row where the conditions are met. Using the INDEX value, we look for the value that is on the range C3:C13, that is on the row that was returned from the MATCH function, in this case, it was row 2, that corresponds to the second row on C3:C13.

All of this examples show you how to use two criteria’s for your lookups. It’s easy to do it if you have more than two, just add then to your formulas. Here how the formulas presented before, would look if you add one more criteria:

=SUMPRODUCT((B3:B13=C16)*(C3:C13=C17)*(E3:E13=C18)*(D3:D13))

=INDEX(C3:C13,SUMPRODUCT((B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18)*ROW(C3:C13)),0)

=LOOKUP(2,1/(B3:B13=C16)/(D3:D13=C18)/(E3:E13=C18),(C3:C13))

{=INDEX(C3:C13,MATCH(1,(B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18),0))}

As you can see, depending on your data tables, you can use several different techniques to lookup your values, using different Excel functions. Hope that you can apply this to your own Excel sheets.

AutoSum

Excel has an AutoSum feature that can save us lots of time when we are working with data tables. Let’s see a practical example. We have a monthly store sales table, like this:

AutoSum1

Now we want to put monthly total sales and a stores year to date total sales. For the monthly sales total, we could put a formula =SUM(B2:D2) on cell E2 and copy down. For the stores year to date total sales, we could put, on cell B7, a formula =SUM(B2:B7) and copy to the right. But all of this can be done using Excel’s AutoSum feature. This feature is so popular that Microsoft putted in two different locations of our menu bar. It can be found on the Home tab, on the Editing group (right side of the menu bar):

AutoSum2

and it can be found on the Formulas tab, on the Function Library:

AutoSum3

To use this feature on our table, and put all of the totals mentioned before, on one single step, we need to select the range of cells with the values and, here’s the trick, we need to select one more empty row of cells (for the stores year to date total sales) and one more empty column of cells (for the monthly total sales). In this example, we need to select the B2:E7 range of cells. After that, let’s click on one of the AutoSum buttons mentioned before. The result will be like this:

AutoSum4

Now we have all of the totals that we wanted. To make this operation even easier to do, instead of clicking on one of the menu buttons, we can use the shortcut keys Alt+= (press Alt key and then the Shift and the key with the = symbol). The result will be exactly the same.

This AutoSum feature can be use to put totals in other ways. For example, if we select only the range of cells with the values (no empty rows or columns), in this case if we select cells B2:D6, and we click on the AutoSum button, we will get only the column totals, placed beneath the first available row below our data, in this case, on row 7. The result will be like this:

AutoSum5

This AutoSum button can be used, instead of sum’s, for other functions like average, count number, etc. You can check the available options clicking on the little arrow on the lower part of the AutoSum button:

AutoSum6

As you can see, the feature is very useful, and very easy to use. It will certainly save you lots of time, when working with data tables.

Conditional Formatting bar chart

I’ve a table of data with target values and actual values, by month, like this:

Conditional_Formatting_Chart1

I want to create a bar chart with this data, something like this:

Conditional_Formatting_Chart2

But I would like, for better understand the data, to see if the actual values, for each month, are above or below the target value. If it’s on target, or over the target, I would like the month bar to show up in green color and, if it’s below target, to show up in red color. This can be done by adding two columns to our table, one for the results “under target” and one for the results “over target”. On this two columns, what we are doing, is to check if the actual sales value is less that the target, then show the actual value; if not, leave the cell blank. This for the “under target” column. For the “over target” column, we are going to check if the actual sales value is equal, or greater than the target value. If true, then, show the actual sales value; if not, leave the cell blank. The result will be like this:

Conditional_Formatting_Chart3

For the “under target” column, put this formula on cell D2 and copy down:

=IF(C2<B2,C2,””)

For the “over target” column, put this formula on cell D2 and copy down:

=IF(C2>=B2,C2,"")

Now we need to change our chart data source. If you right click on your chart and select “Select Data”, it will open the “Select Data Source” dialog box, like this:

Conditional_Formatting_Chart4

As you can see, your chart has only one data series. We are going to work with two data series: one for the “under target” values and one for the “over target” values. To start, we are going to change the series values for the existing series. For that, click on the Edit button on the Series box, on the left of the dialog box. This is pointing to column C, as you can see on the image below. We need to change the “Series name” to “=Sheet1!$D$1” and the “Series values” to “=Sheet1!$D$2:D$4” and click the OK button.

Conditional_Formatting_Chart5

Our chart series in now point it out to the “under target” column. We need to add the second series for the “over target” values. Click on the Add button, on the Series box:

Conditional_Formatting_Chart6

Fill out the “Edit Series” dialog box, like this, and click on the OK button:

Conditional_Formatting_Chart7

As you can see, you need to specify the “Horizontal Axis Labels” for this new series because it’s not showing up the months names. Just click on the Edit button and select the “=Sheet1!$A$2:$A$4” range.

Conditional_Formatting_Chart8

Close the “Select Data Source” dialog button, by clicking the OK button, and your chart should now look like this:

Conditional_Formatting_Chart9

We need to change the color of our series bars to display the “under target” values as red bars and the “over target” values as green bars. Just right click on the “over target” bars and select “Format Data Series”. Go to the Fill group and select “Solid fill” with a green Fill Color. Click the Close button when finished.

Conditional_Formatting_Chart10

Do the same for the “under target” series but for this select a red Fill Color. Before clicking on the Close button, select the “Series Options” group and on the “Series Overlap”, put 100% so that the chart bars appear center between each month.

Conditional_Formatting_Chart11

When you click the Close button, the result should be like this:

Conditional_Formatting_Chart12

Try changing any value on the B2:C4 range to see the conditional formatting of the chart adjust the colors.

Add new data to an existing chart

This is a common task on Excel. We have a chart and we need to update the data displayed by adding more data. For instance, we have a chart that displays data from January to March and we need to add data for a new month. Let’s see how we can do this. We have this existing chart:

Add_new_data_chart1

Now lets add a new column to our table to add the April sales data, like this:

Add_new_data_chart2

There are several ways of adding and additional column of data to our chart. Let’s see some:

Method 1: This is the easiest way of doing this but it’s now always possible to to. Instead of putting the new data on the right side of the last column, insert a new column between columns C and D, copy the March data into this new column and put the April values on column E. Because you are adding a new column inside the source data range of the chart, Excel will adjust to accommodate this new column. As I said, this is not always possible if you have more data below this table. If you insert a new column, it will mess the data below.

Method 2: Put the April data on column E. Select cells E1:E4 and press Ctrl+C (Copy). Then click on the chart to select it and press Ctrl+V (Paste). You will have your new data displayed on the chart, like this:

Add_new_data_chart3

Method 3: This is the usually method that most people use. Right click on the chart and select the Select Data option, of go to the Design tab and on the Data group, click on the Select Data button.

Add_new_data_chart4

A Select Data Source dialog box opens and the chart source range get’s highlighted with the “marching ants”.

Add_new_data_chart5

With the cursor on the “Chart data range” field, just select the new data range, in this case select A1:E4. Click the OK button and your chart will be updated with the new data.

Method 4: Click on the chart. If you look at your table now, you can see blue, green and violet lines around the cells.

Add_new_data_chart6

If you pass the mouse on top of the blue line, it will get thicker showing you the actual source data of your chart, like this:

Add_new_data_chart7

Just drag the line, pushing from one of the blue squares on the corners, so that you get the new range of data selected, in this case range B2:E4 and release the mouse button so that your chart gets updated.

As you can see, there are many ways of doing the same thing in Excel. You just need to choose the one that you like it more.

Add automatic subtotals

This is a very handy trick. We have a table, like this:

SubTotals1

We now want to get the subtotals of the Value column, for each Store. First thing that we need to do, is to sort the table by the Store column. The easiest way is to click on any cell on the column (in this case, C2:C12), go to your Data tab and click on the “Sort A to Z” button, like this:

SubTotals2

We now have our table sorted by the Store column. To add the subtotals, on your Data tab, you’ll find a Subtotal button, on your Outline group. Click it and a Subtotal dialog box will open, like this:

SubTotals3

Now you can choose what is the column that you want to group (“At each change in”), in this case is the Store column, what is the function you want to use, we want to Sum, and what is the column that you want to sum, that is the Value column. Just click OK and your sheet will now look like this:

SubTotals4

As you can see, you now have an Outline column to the left of your sheet and a subtotal for each Store and a Grand Total in the end. Let’s see what you can do with this new Outline columns. Click on the “1” button, on the top of the Outline column and you will hide all of the details on your table, leaving you only with the Grand Total, like this:

SubTotals5

If you click on the “2” button, you will get only the subtotals by store and the Grand Total, like this:

SubTotals6

If you click on the “3” button, you will get your complete table again. You can always click the “+” and “-“ buttons to expand or collapse some of your groups. If you want to remove your subtotals, and go back to your original table, just select a cell on your table, go to the Data tab, click the Subtotal button again and on the dialog box that opens, click on the “Remove All” button.

Copy visible cells only

You have a table of data and you hide some rows and columns and you want to copy only the visible cells into another sheet? I’m going to show you some techniques that you can use to do that. Let’s see how our original table looks like:

Copy_Visible_only1

Now, let’s hide some rows and columns so that our table looks like this:

Copy_Visible_only2

If you select the entire table (cells A1:E12) and just copy and paste (Ctrl+C and Ctrl+V) it into another sheet, it would copy all rows and columns and would look like the original table. That’s not what we want to do, we just want to make a copy and show only the 7 rows and 4 columns that we see on the image above. For that we need to select the visible cells only so that we can copy only those. Let’s select again cells A1:E12 but this time we’re going to select first only the visible cells, before copying. We can do this by just using the shortcut Alt+; (semicolon) or you can go to the Home menu bar, on the Editing group, and click on the Find & Select button and choose Go To Special option.

Copy_Visible_only3

Instead of going to the menu bar, you can just press F5 or Ctrl+G to open the “Go To” dialog box and then just press the Special button on the bottom. On the Go To Special dialog box, select the “Visible cells only” option:

Copy_Visible_only4

This will select only the visible cells, as you can see on this image:

Copy_Visible_only5

Now you can just Ctrl+C (Copy) and Ctrl+V (Paste) it into another location. This will only copy the visible cells, like you wanted.
If you are working with Filters, you don’t need to use this techniques because, when you copy filtered data, it only copies the visible cells already.

Fill Blanks on column

This is a common need for Excel users. Let’s imagine that you have a table, like this:

Fill_Blanks_nonVBA1

As you can see, only the first record, for each city, is filled on column A. If you needed to sort this table, you would loose the information of the City on the rows where column A is not filled. So, you need to fill the blanks on column A.

To start, let’s click on column A header to select this column. Then press F5 or Ctrl+G to open the “Go to” dialog window. Here, click on the “Special…” button and, on the dialog window that opens, select “Blanks” option, like this:

Fill_Blanks_nonVBA2

Click the OK button and you will have only the blank cells of column A selected, like this:

Fill_Blanks_nonVBA3

Now, on your formula bar, write “=” and press the first filled cell on column A, in this case, cell A2, like this:

Fill_Blanks_nonVBA4

As you can see, the formula that you’ve entered on the formula bar, is being written on cell A3, because it’s the first cell on the ones that are selected. Now, instead of pressing just Enter, press Ctrl key and then Enter (holding the Ctrl key) to insert this formula in all of the selected cells. The result will be like this:

Fill_Blanks_nonVBA5

You now have all of your blank cells on column A filled with the value from the first filled cell above. Now you need to convert the formulas to text. For that, just select the cells on your column A, in this case range A2:A12. Click on the edge of the selection range with your right mouse button and drag it slightly to the column to the right and then back to column A and release the right mouse button. A new option menu will appear, like this:

Fill_Blanks_nonVBA6

Select the “Copy Here as Values Only” option and your formulas will be replaced by the cell values. Now you can use your data table is ready to be used on sorts.

“VLOOKUP Great White Shark Award”

vlookupshark_250x102My participation on the “VLOOKUP Week” (March 25th to March 31st) was with my article about Cascading Validation Lists. It was chosen by Bill Jelen (aka Mr. Excel) as one of the seven most innovative that was posted. It was then voted on the Microsoft Office Blog by the users and won. I received the “VLOOKUP Great White Shark Award”. Thanks for those that voted on me. I will continue writing articles that can help you learn new tricks on MS Excel.

Hide values on column for better looking when printing

This is just a simple trick that can help you improve your tables printing. Imagine that you have a table like this below:

Hide_Values_column1

Many people prefer not to print the tables with the same stores location repeated on column A, for each line. Many would prefer to print the table looking like this:

Hide_Values_column2

To achieve this, just click on column A, to select the entire column, and then, on the Home menu tab, click on the Conditional Formatting button and then on New Rule option. Then, on the dialog box that opens up, on the “Select a Rule Type” field, choose the last one “Use a formula to determine which cells to format”. Now, on the “Format values where this formula is true” field, we will enter =A1=A1048576, like this:

Hide_Values_column3

This will tell Excel that if any value on column A is equal to the value above, then this formula is true. We use 1048576 because it’s the number of rows that Excel 2010 has, has you can see here. Then we need to set the format to apply when the formula is true. Because we want to hide the cell content, in this case we will use a white font color, so click on the Format button and, on the Font tab, on the Color field, choose the white color. Click OK button twice to finish. Your table will look like the second example that I showed here.

The values on column A are still there, in each row, but they don’t show up on the printing. If you select column A, you can see that all of them are still there, as you can see on this image:

Hide_Values_column4

The advantage of keeping the values is that, if you need to do a sort on your data table, you can do it without loosing the correlation between the rest of the columns with column A. As you can see, this is a very small table, it’s easy to do this for each store location but if we were talking of a table with thousands of rows, this trick will save you lots of time.