Selecting Non- Contiguous Cells

CTRL Key

I was talking to someone the other day who had been using Excel for years but was unaware of how to select non-contiguous cells so I thought I would explain it in case you were interested. It is extremely easy.

• You select the first cell or range of cells that way you normally would.
• Then you hold down the CTRL key and select the next cell or range of cells.
• If you have another row select it (still holding down the CTRL key)
• Deselect the CTRL key when you have finished.

Yup.. that is all there is to it.

So, what can you do with this knowledge?

• Well, I love to you use this when charting.Just select the non-contiguous ranges of data you want to chart and then click Insert and select your chart type. Deselect the CTRL key.

• This is also useful if you want to format the top and bottom row of your P&L as currency. No point in selecting the top row and formatting it and then moving to the bottom row and formatting. Instead select the top row, hold down the CTRL key and select the bottom row and then apply the formatting. When finished just deselect the CTRL key.

• This also works if you have a couple of non-contiguous columns that you need to sum. Just select the first column, hold down the CTRL key and select the next column and then the next column and the next column etc. Click the AutoSum icon and all the columns will display totals.

Protect Your Workbook Structure

Protect Your Work

If you share workbooks with others then I am sure you have experienced someone inserting or deleting a sheet and totally messing up your work. Or, that helpful person who renames the sheets for you??

Anyway, in addition to protecting the cells in the sheet which I have covered in other blogs, today I want to talk about protecting your workbook's structure. It is easy to do and you will wish that you knew about this a long time ago.
These steps pertain to the entire workbook - not just a worksheet.

• Click Review. 
• Select  Protect Workbook which is located in the Changes grou.p
• Select Structure Option in the Protect Workbook dialog box.
• 4. Click OK.

If you right-click on a sheet name after doing these steps, you will see that you can no longer delete, insert or rename a sheet. In addition, you cannot change its color or hide or unhide the sheet.

If you select Windows, you can prevent users from changing the size or position of worksheet windows.



This tip is very useful if you are linking together several spreadsheets maintained by others.

TRACKING FORMULA ERRORS in SPREADSHEETS
Guest Post: J. Helstrom

Spreadsheet formula errors may cause other calculations to also yield an error message.

This is aggravating, especially if you’re working with a large worksheet.

Formula errors can be fixed -  the hard part is finding all of them.

Luckily, Excel provides a handy tool for identifying formula errors.  It’s embedded within the Find & Select icon on the Home ribbon.

Press Find & Select, then click on Go To Special…

  or press the  F5 key and then click Special....

The following dialog box appears:

Click the Formulas button and then deselect everything except the Errors check box as shown below:

Click on OK and all errors in the worksheet are now highlighted.

As an example, assume that a worksheet contains the following errors:

When Find & Select…Go To Special…Formulas…Errors is selected as shown above, the result is:

Excel has highlighted all the error messages. 

If you haven't used Go To Special before - take a look at it. You can do a lot of cool things with it such as selecting visible cells only and  it also allows you to find cells containing Conditional Formatting and Data Validations among other things.

JOINING VLOOKUP FUNCTIONS TOGETHER

JOINING VLOOKUP FUNCTIONS TOGETHER


Between the holidays, Ice Storms and dealing with my daughter's wisdom teeth removal, it has been a little while but I am still working away on my VLookup Ebook and I have to tell you I have learned some cool tricks. 

This tip is a bit basic but I am willing to bet some of you have never considered it.  It is a really simple idea but one that never really occurred to me until I started really looking at Vlookups.

Instead of creating two Lookup functions and then creating a 3rd column to multiply or add the numbers together – you can do it all in one.
In the example below, my Order information is in Columns C through H and the lookup table containing my data is in Columns K through R.

My lookup value is Product ID which is in Column E. I want to find the Price in Column Q and the Shipping Cost in Column R for each product and then multiply it by the quantity of product being shipped.

In other words, I want to add together the Price (Column Q) and the Shipping Costs (Column R) for each Product ID in Column E and then multiply it by the Quantity in Column G.

To do this, I clicked in cell H3, Total Price,  and created a Price Vlookup  to look up the Price in Column Q, typed a plus sign and then created a second Vlookup to retrieve the information in Column R. Then I put parentheses around the entire equation and multiplied it by G3 (Quantity)and then copied it down.

The equation is: =(VLOOKUP(E3,$K$3:$R$30,7,FALSE)+VLOOKUP(E3,$K$3:$R$30,8,FALSE))*G3

Just a little faster way to do the calculation. Have fun.

Using Vlookup with other functions

LOOKUP FUNCTION

As I mentioned, I am writing a new Ebook on Lookup Functions and am having a blast playing around with some of these formulas.
 I started with LOOKUP. I have always wondered about it as I have always used VLOOKUP and HLOOKUP but had never seen LOOKUP used.

The Excel LOOKUP function has two forms: the Vector Form and the Array Form. Today, I am just going to talk about the vector form. This 

allows you to lookup a single value from a column. 

The lookup function is pretty straight-forward although you would never know it from its syntax.

=LOOKUP(Lookup_Value), lookup_vector, result_vector)

So, in English  this translates as:
 =LOOKUP(value you looking up, column that contains the value you are looking up, the column that contains the answer).

In the example below, I have an employee listing and I want to find the salary of Hamilton.  So, I am looking up Hamilton which is in Column A; that takes care of the first 2 parts of the syntax – the lookup value is A5 or “Hamilton” and the lookup_vector is Column A.
 The result or answer I am looking for is in Column B – Salary.

The equation then is =LOOKUP("Hamilton",A4:A9,B4:B9)
 

Now, this seems pretty simplistic with only 6 rows of information but you would find it useful if you had a couple of hundred or more employees. The difference between this lookup function and VLOOKUP or HLOOKUP is that there is no separate lookup table; instead, you are looking in the data itself.