Excel Tips

How to increase or decrease values in excel without using formulas

2011-05-31T20:02:00.000-07:00

Let's say you have to increase all the values in a worksheet to 10%. You can of course use a formula and multiply the value by 1.10. There is an alternate way, however, that you might like to use.
This will be useful specially when you need the new values without the formula. Use the excel paste special function. Yes, you've read it right,the paste special function!

Here's how to us this tool. First input the value in a cell, in this case the value 1.10. Copy this cell, then select all cells which contains the value to be changed and then right click. Choose paste special.

A dialog box will come up. Choose "multiply".

Click "OK". Now you're done! All your values are now 10% higher. All values are multiplied by 1.10.

This can also be used to add, subtract, and divide the values by clicking the appropriate option buttons.

Happy computing!

Excel through the years

2011-05-31T19:47:00.000-07:00

Let's take a break from our excel tips and look a bit about microsoft excel history. Below are images of how previous versions of excel looked like. A trip down excel memory lane.

1987- Excel 2.0

1990 - Excel 3.0

1992 - Excel 4.0

1993 - Excel 5.0

1995 - Excel for Windows 95 (version 7.0)

1997 - Excel 97

1999 - Excel 2000 (version 9.0)

May 31, 2001 - Excel 2002 (version 10.0)

October 21, 2003 - Excel 2003 (version 11.0)

November 30, 2006 - Excel 2007 (Version 12.0)

Excel 2010

There it is. Excel through the years!

Autosum shortcut

2011-05-31T07:17:00.000-07:00

One of the most used formulas in excel is the sum function. Do you know that there is a shortcut that you can use to sum up a column or row of data aside from typing the =sum( ) function? Well, aside from clicking the autosum button , you can use this shortcut:

"Alt+="

It will automatically sum all the numbers for the rows or the column.

Try it.

Happy computing!

5 Useful excel formulas for manipulating text

2011-05-27T07:42:00.000-07:00

Excel has a lot of useful formulas for manipulating text. I'll cover some of the more useful ones. And most of which I often use.

1. Convert text to upper case.

Excel has a function to convert text to all uppercase. And it is so easy, the formula is simply:

=UPPER ("text")

so to change a text in let's say A1, then just use =UPPER (A1). If cell A1 contains "excel", it becomes "Excel" on the cell with the formula.

2. Convert to proper case.

Sometimes, you want to change to capital all the first letters of a phrase, sentence or group of words such as titles. Here's a way to do this, use the formula:

=PROPER("text")

For example, if the text is "excel", then it becomes "Excel". If it is a sentence, a phrase, or group of words, it will capitalize the first letter of the first words. Example, "happy computing everyone" becomes "Happy Computing Everyone" (Quite useful for titles).

3. Extract left part of a text.

If you want to extract part of a string then you can use this formula:

=LEFT("text",number of letters)

For example if cell A1 contains "ExcelEngineering" and you want only to get excel, then you can use this formula to extract "excel",

=LEFT(A1,5)

in other words, get 5 letters, starting from the LEFT from the text in A1..

4. Extract right part of a text.

Very similar to the post above, but instead you start counting from the right.

=RIGHT("text",number of letters)

If cell A1 still contains "ExcelEngineering" and you want only to get "ring", then you can use this formula to extract "ring",

=RIGHT(A1,4)

in other words, get 4 letters, starting from the RIGHT from the text in A1..

5. Extract anywhere in the text or string

If you want to extract a text starting from any point in the string - not necessarily from the right or from the left, then use the function:

=MID(text,position, number of letters)

Using the same example of A1 with the word "excelegineering", if you want to extract the word "engine", then use this formula:

=MID(A1,6,6)

in other words, start on the 6th letter of the text in A1(starting from the left) and get 6 letters.

Happy computing!

Add a Drop-Down List in Excel 2007,2010

2011-05-20T21:55:00.000-07:00

Ever wondered how some worksheets have some drop-down list? Do you have to learn a very complicated macro or programming to do it on your own worksheet? Worry not, it is so easy. After learning this, you will be able to add this to your worksheet and make your worksheet look professional (and hopefully more user friendly). Here's the procedure.

Input all the data you want included in the drop-down list in a series of cells (example: A1:A5)
Click on the DATA tab in the ribbon
Select Data Validation
On the settings tab of the data validation dialog box, under "allow", choose list
Under the source box, input the list of data (in this case A1:A5)
Now, you have a drop-down list. Well done!

Data validation on the ribbon

Data validation dialog box

Drop-down list in excel

Now, your worksheets will look more professional and you'll look more like an excel expert!

Happy Computing!

Automatically Insert Current Time and Date in a Worksheet

2011-05-20T21:45:00.000-07:00

If you want to display the current time and date in excel automatically, then just use this formula:

=Now()

This will return the current date and time in the format that you set in your windows preferences. If, on the other hand, you only want to see the date, then just use this formula:

=Today()

Here is a sample output of each formula:

Now() : 5/21/2011 12:41
Today() : 5/21/2011

Happy Computing!

How to use Excel VLOOKUP

2011-05-20T20:33:00.000-07:00

One of my most used formula in excel is the VLOOKUP formula. Basically what this formula does is that it returns a certain value from a table given a specific value.

A very simple example would be like this.
Assuming you have this table:

If you want to choose from the table given the number, then we can use VLOOKUP this way.

=Vlookup("1",A1:B4,2,False), this will result in "Apples"

In layman's terms, it means

=Vlookup("what to lookup for?", "what table?", "what column to get the results", "don't expect sorted data")

you can use a formula at the first argument to reference to another cell.

Now try it!

Happy Computing!

Quick Auto Sum in Excel

2011-05-20T09:15:00.000-07:00

Want to use a keyboard shortcut to quickly sum up a column or row of data? Here's how:

Go to the end of the row or column (first cell with no values)
Press Alt-= (Alt and equal sign)
Auto sum is immediately inserted.
Done!

Quick. Effective. Simple.

Happy Computing!

Quick Navigation in Excel (Control Key)

2011-05-20T09:08:00.000-07:00

If you want to go to the last data in a row or column, simply use this combination:

Ctrl-Arrow key.

Example, if you are at the top of the column and you want to go to the last data of that column (before any break), then use the Ctrl-Arrow Down. If you were at the bottom, then use Ctrl-Arrow up.

Same rules for Ctrl-Left Arrow and Ctrl-Right Arrow.

Now it's quicker to navigate highly populated sheets!

Happy Computing!

Highlight All Cells Referenced by a Formula

2011-05-20T09:00:00.000-07:00

Here's a quick shortcut to highlight all cells referenced by a formula. Go to the cell with the formula then hit

Ctrl-[ (control and open square bracket).

You will see all referenced cells highlighted. To move to the next selection (highlighted cell), simply press enter.

Very useful for auditing worksheets!

TIP: If you want to determine the OPPOSITE of it- that is formulas that reference the current cell then simply use:

Ctrl-] (control and close square bracket)

Very useful for auditing sheets!

Happy computing!

Add Bullets in Excel

2011-05-19T17:57:00.000-07:00

Ever wondered how to add bullets in excel? It's so easy to do it in microsoft word. But how about excel?
Here's how.

On the selected cell, press Alt-0149. It will insert a bullet. Continue doing so for other cells or just copy paste the bullet and then add the text.

Alt-0149
Copy paste
Or just repeat the key combination.

Works on all excel versions.

Happy computing!

Display all formulas in a sheet with a single key combination!

2011-05-19T10:53:00.000-07:00

Want to show all formulas within a sheet with just a sing key combination? Yes! With this combination, you can toggle this to display the results or the formula.

Here it is Ctrl - ~ (it's ctrl key and the tilde, key before "1").

That's it, you can now conveniently see and hide the formulas in your sheet.

Works with any version of excel.

Happy Computing!

Copy as Picture in Excel

2011-01-04T18:53:00.000-08:00

Excel2007:

Want to copy texts and graphs as picture? Here's how.

1. Click Paste -->As Picture--->Copy as picture.

2. A dialog box will then pop-up, giving you a choice to copy as picture or bitmap and if copied as shown on screen or when printed.

3. Choose between the appearance and the format

4. Paste as usual.

5. Done!

Excel 2010:

1. Go to home tab, copy, the select "copy as picture"

2. A dialog box will then pop-up, giving you a choice to copy as picture or bitmap and if copied as shown on screen or when printed.

3. Choose between the appearance and the format

4. Paste as usual.

5. Done!

Excel 2003:

There is a secret menu for this and this is how to get it.

1. Press the shift button while clicking on the Edit menu. There, a new menu will come up - "Copy as picture".

2. Click on this. The same popup dialog box will appear.

3. Choose between the appearance button (as printed or as seen on screen) and format (as picture or bitmap).

4. Paste as usual.

5. Done!

What for? Well, this will ensure that your graphs and your text will be consistent in its size and formatting when you paste it in the same sheet or on another office document such as word or PowerPoint.

This could be very useful when doing reports and presentations.

Happy computing!

Shortcut to Format any Number in Excel

2011-01-03T08:32:00.000-08:00

Here's some shortcut to easily convert a number to any format.
First, when you add a number in an excel cell, it defaults to a "general" format - "1234". To quickly change it to a "number" format - 1,234.00 here's what you do:

   1. Click the cell with the number
   2. Press Ctl-Shift-1
   3. Done!

Easy!

Other combination and formatting results are as follows:

   Ctl-Shift-2: Hour - 12:00AM
   Ctl-Shift-3: Date - 18-May-03
   Ctl-Shift-4: Dollar - $1,234.00
   Ctl-Shift-5: Percent -1234%
   Ctl-Shift-6: Scientific Notation -1.23E+03

Happy Computing!

Hiding the Excel 2007 Ribbon Shortcut

2011-01-03T00:51:00.000-08:00

Using a netbook and want to save some screen space? Try to hide the "Ribbon". The ribbon is where the menus in excel 2007 are.

To quickly hide and unhide it, just use Ctl-F1. That's it!

Happy computing!

Wrap Text in a cell

2010-01-31T02:58:00.000-08:00

Want to quickly wrap text in a cell? Simple. Use alt-enter to drop the next text. Do as many times as needed. It's that easy!

Quick Redo in Excel: The Mighty F4

2010-01-30T16:32:00.001-08:00

The Mighty F4. One of the most useful shortcuts I've learned is the F4. How is it used? Well, it is basically a keyboard shortcut for redo. Here's how it works, or at least the way I use it.

When you want to do something repetitive but does not warrant a creation of a macro, then use the F4. Example, you want to color different non-contiguous cells to blue. You could click the cell, format it to blue, click another cell, format it again and so on. Another way,quicker in my own opinion is to click a cell, format it, then click all other cells and press F4. This will definitely save some time.

This is specially true for tasks that require a lot of "mouse work" or clicks. You only do the "dirty work" once and then just F4 all the rest.

Try it out, you may like it! Hope it helps you well and make your work a little easier and quicker!

Happy computing!

Excel Calendar Templates for Free!

2010-01-30T09:33:00.000-08:00

It's a new year and of course a new calendar always comes in handy.

Enter vertex.com, I've seen this site a few years back trying to find some excel calendar templates and I've never looked at another site again! It has all sorts of useful templates and most are free such as this template.

Almost all types of calendars are available. Check the site and you'll almost always find what you need or look for.

Head to this link for the calendars (and other very useful templates). Kudos to the creator of this site!

http://www.vertex42.com/ExcelTemplates/excel-calendar-template.html

Fractions in Excel?

2010-01-30T09:28:00.000-08:00

Yes, you read it right. If you use excel 2007, you can format decimals to fractions!

more on this on the microsoft site.

http://www.microsoft.com/Education/DisplayFractions.aspx

Generate Random Numbers

2009-01-12T03:25:00.000-08:00

There are two ways to generate random numbers in excel automatically:

Use the rand() function. It generates a random number from 0.1 to 0.9. To refresh just use F9. You can use "=round(rand()*100)" to generate whole numbers from 1 to 100. Or if you want, you can also use any other number as the multiplier.
Easier still, you can use "=RandBetween(a,b)". Where "a" and "b" are the least and max numbers that you want to generate. For example, if you want to generate a random number from 1 to 100. You will have to use =randbetween(1,100).

Hope this helps!

Happy computing!

Quickly Add New Series in a Graph

2008-09-23T17:58:00.000-07:00

Want to add another series in a graph, a quicker way? Here it is. Instead of right clicking on the graph, choosing source data and use the add series, you could do this.

Select the series to be added.
Point the cursor to the side (until it becomes an arrow).
Click and drag to the graph.
You have successfully added the series!

you can then change the labels. You can also do this for other series. Also, you can select more than one column or row at a time and drag.
Happy computing!

Copying Sheets the Quick Way

2008-09-22T01:47:00.000-07:00

Want to have different sheets with the same or similar content? Here's one way to do it! Of course, you could always select all, copy, then paste to the other sheet. But, here's a quicker way (for me anyways).

CTL-LEFT CLICK the sheet you want to copy and then drag. You'll get an exact copy of the sheet with a name "OrigName(1)", "OrigName(2)", etc.

What's the use of this?Well, for one, if you should have similar sheets every month. You can create a sheet with the needed format, then do this copy sheet and just rename the sheets afterwards.

Hope this helps! If you have other ways, don't hesitate to share. We're all here to learn and share!

Finding a Maximum Value with a Condition ("MaxIF")

2008-09-22T01:35:00.000-07:00

Very similar to last post. the "MinIf" function, if you want to get the maximum value with a condition then use this formula!

Assume data is in A1:A5 and want to get the maximum value BELOW 12,000, then.

=Max((if(A1:A5<12000,a1:a5,false))

Again, use CTL-SHIFT-ENTER to convert into an array formula and your done!

Minimum Value Excluding Zero ("MinIf" function)

2008-09-19T02:46:00.000-07:00

Ever confronted trying to find the minimum value excluding zero? Specially using downloaded metered data, there are times when you want to get the lowest value (with the exception of power interruption in which case it is zero). So how do we go about it in excel?

Here's the formula!

Assuming data is in cells E11-E18, then:

=MIN(IF(E11:E18>0,E11:E18,FALSE))

Basically, this formula says that, get minimum value IF the number in the array is greater than zero. If it is zero or below, do not consider it.

IMPORTANT NOTE: then instead of using only the enter key, use CTL-SHIFT-ENTER. This will convert the formula into an ARRAY FORMULA which will make this work. You can see if it is succesfully converted to array formula if you see a bracket enclosing the formula.

This formula can also be used with different conditions. This is one "MIN IF" formula.

Hope this helps! Note: You can also use this concept to create a "MAX IF" formula.