Six great Excel tips

Author: mety Nagm Labels::



Tip 12: Change dates like 20050923 to one Excel can "understand"

Suppose you are looking at a worksheet which contains dates which can't be formatted as "real" dates because of their structure:

Book image

Select the date(s), and use Data tab, Text-to-Columns, [Excel2003:DataText to Columns]

Book image

Which brings up the Text-to-Columns wizard:

Book image

Even though the dates are fixed width, in this case you can simply click "Next" twice.
In step 3 of wizard Select Date, YMD:

Book image

If you click Finish now, the result will replace the dates. You can select another destination cell:

Book image

Here's the result:

Book image

Tip 16: Using Pictographs


Anything in the clipboard can be pasted onto a chart

Book image

Clicking on the coins, copy (ctrl/c), click on a series, Home/paste (or ctrl/v):

Book image

Right-click on the series, use Format Data Series:

Book image

Click these options: [Excel 2003:Patterns tabFill Effects]

Book image
There are 3 format options:
  1. Stretch (default)
  2. Stack
  3. Stack and scale to

If we select Stack, we see:
Book image
If we select Stack and scale, we can enter a number into the Unit/Picture box. Again, this is something you can experiment with.
You also may have noticed these options in this dialog:
  • Gradient fill
  • Picture or texture fill
  • And others

Here's the gradient option selected and the Preset colors dropdown:
Book image
The new chart:
Book image
On the picture tab there was a button to select a picture−you can use any picture you have on your computer! You can experiment with the other tabs as well.
Top of Page Top of Page

Tip 29: Formatting comments

When most people create a comment (Review Tab, New Comment, or Shift/F2), [Excel2003:InsertComment], they accept the shape and color and just enter the information they want. So most comments look something like this:
Book image
But how about a comment like this?
Book image
Or even this: (It's me!)
Book image
Here's how you can do it:
When you first create a comment, the cursor is right after whatever you typed. You need to select the comment itself, not the text inside. So, select the border of the comment.
Book image
You need the "Change Shape" tool. This can be placed on the Quick Access toolbar: [Excel 2003: ViewToolbars DrawingDraw MenuChange AutoShape]
Book image

When the comment is selected by the border, click the new Change Shape tool.

Book image

From that, you can select basic shapes, block arrows, flowcharts, stars and banners, or callouts, each of which has yet another set of shapes to choose from. Shown here is the Cloud callout:

Book image

Once you have the new shape, (cloud, here), right-click the border once again, and choose Format comment as shown, if you wish to format this comment even further:

Book image

That will bring up yet another dialog, from which you can choose Fill Effects:

Book image

From this dialog box you can select the Picture tab and import any picture .

Book image

As you saw before.

Tip 39: Selecting a random sample of data

If you have a database with many records and you want to take a random sample of that data, here are a few techniques you can use.
One way to get a random sample is to use a computed criteria and advanced filter.
Suppose you want to take a random 10% of the data. Enter the formula as shown in C2 (keep C1 blank). By entering the formula = RAND()<0.1, every time this worksheet calculates, the =RAND() will return another random number. So RAND()<0.1 will return true, about 10% of the time.
(Rand() returns a random value between 0 and 1, not including 1).

Book image

Using the Advanced button from the Data tab: [Excel 2003:DateFilterAdvanced Filter]

Book image

You can filter like this:

Book image

and that will create a random selection:

Book image

This will be different each time. You may notice that there are only 9 items shown not 10, and that's because the values RAND returns are random! It's best to use this on larger databases!
A second way to select a random 10% of your data is to still use the RAND function, but not use filtering. Look at this:

Book image

Cells B2 thru B101 contain = RAND(). All you need do is select A2:B101 and sort by column B! Take just the first 10 items, and you have your random 10% of the database!

Tip 55: See all characters in font set

  1. Enter =CHAR(ROW()) in row 1
    1. =ROW() return the row you're in. =ROW() entered in cell G23 returns 23.
    2. =CHAR(97) returns the 97th character in the character set for that font, usually a lowercase "a" (picture fonts like Wingdings or Webdings return something else).
  2. Fill down to row 255.
  3. Easy to see things like ●, ¢, £: Book image
  4. When you see that "●" is in row 149, you can then know that holding Alt while typing 0149 on the numeric keypad will create this character as soon as you let go of the Alt key! And this holds true for all of Office, not just Excel! You can copy/paste special characters for the character(s) you want, and you can then copy the resulting character from the formula bar.

Tip 74: Bringing the selection into view

It's possible you've selected some areas of cells and the scrolled away so you can't see it any more. CTRL/Backspace brings that selection into view, and shift/Backspace brings selection into view as well but reduces the selection to the active cell. So, if this is the before picture:

Book image

Then shift/backspace will simply have cell B3 selected.

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2