Grabbing the right case

Excel has three functions that handle case. LOWER,  UPPER and PROPER.

See image below for the results of using these functions. Cell A2 contains an entry.

case_1

Cell B2 has =LOWER(A2)
Cell C2 has =UPPER(A2)
Cell D2 has =PROPER(A2)

Note: The PROPER function capitalises the first letter of every word in the cell – see below.

proper function

Checking if entries are really the same

To check if two cells are the same you can use

=A1=B1

This returns TRUE if the values are the same or the text is the same ignoring case sensitivity.

In the above formula TEST will equal test.

To check if entries are identical, taking into account upper and lower case, then you can use

=EXACT(A1,B1)

In this case comparing TEST and test will return FALSE.

When I examine myself and my methods of thought, I come to the conclusion that the gift of fantasy has meant more to me than my talent for absorbing positive knowledge.

Albert Einstein (1879-1955)

Free Excel Webinar Recording

FREE – Excel Mouse Shortcuts

Following up from my keyboard shortcuts session in February. This month we will look at quick ways to do common tasks with the mouse. Some of the topics and techniques I will cover include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard. Even if you prefer using the keyboard you might learn a few useful new techniques.

Latest Free Excel Webinar Recording.

Achievement is largely the product of steadily raising one’s level of aspiration and expectation.

Jack Nicklaus (1940-)

Formatting Pivot Tables

When formatting a Pivot Table is best to right click the values section of the report and choose Number Format.

PT_Format_tip

This will format the whole report, not just the selected range. Avoid using the Format Cells option as it only applies to the range selected.

Don’t be afraid to take a big step if one is required. You can’t cross a chasm in two small jumps.

David Lloyd George (1863-1945)

I am only one; but still I am one. I cannot do everything, but still I can do something; I will not refuse to do the something I can do.

Helen Keller (1880-1968)

How to Copy a Sheet

The easiest way to copy a sheet is to click and hold on the sheet tab name and then press and hold the Ctrl key down and move the mouse to where you want to paste the new sheet and release the mouse.

When you press Ctrl a small plus sign will display in the document icon to show it is copying, not moving the sheet.

Copy sheet