a simple essay

Feb16

Simple Microsoft Excel Tips

We all know Word, its Microsoft’s popular word processing application. Along with word, Microsoft also maintains a suite of office programs including Excel, PowerPoint and many others. All of these programs have become popular because they are easy to use yet have so much power and flexibility. Unfortunately, because they have perhaps a little too much power, it’s not easy for anyone to sit down one day and find out how to use some of the applications more powerful tools.

Take Excel for instance, even if you’ve only used paper spreadsheets you can intuitively make dynamic sheets not possible in paper. You can do this in part thanks to Excel’s ability to make formulas in a cell. To make a formula just begin by typing “=” in a cell and follow it with a mathematical expression, like A1 + B2, or with some of Excel’s built-in functions. SUM is a well known built-in function what can sum all the cells in a range (a range of cells in excel is represented by a beginning cell, a colon, and ending cell). However, there are other functions in Excel that you can use, by pressing the fx button next to the formula bar (the bar where you change the contents of a cell on top of the spreadsheet) you can search through all the functions in Excel.

The magic about formulas is that you can mix and match and make some complicated formulas. Say you have a table of objects, customers number of objects sold to that customer. If you want to find out how many of a specific product a customer bought you would need to do a conditional sum. Using the SUM and IF functions you can make a conditional sum like so:

=SUM(IF($A$3:$A$11="Sofa", $C$3:$C$11, 0) )

Just remember to press Ctrl + Shift when pressing enter or else Excel will complain about incorrect values. Conditional Sum Example.

The SUM function will add all the values that the inside function IF returns. The IF function is divided with comas into three parts, and the logic is simple enough: if the first part is true then do the second part, else do the third part. So the first part is what we are checking, in this example we want to test if the cells in the range A3-A11 have the word “Sofa”. We then add the range C3-C11 because if the adjacent cell in A has a sofa, then we want to know how many sells there were. And in the last part we just return 0 because if there was no sofa in A, we aren’t very interested in adding the amount sold. It should be noted that in the second and third part, we can instead return expressions instead of the constant values or values of cells.

In this matter, you can make many formulas to do some very complicated things in Excel. But there are other things in Excel to make your life easier, Excel 2002 has an extensive collection of shortcuts to help you navigate and fill out cells. Type a number in a cell, and then move your cursor to the lower right hand corner of the cell and the pointer will change to a black cross. Left click and hold as you move your mouse down. When you have the range that you want, let go, and all those cells will now have the number you typed. The button that appears on the lower right of the selection will let you change the selection to fill in the series of numbers. You can use this with other cells too, not just number cells.

The following are some Excel Shortcuts

Ctrl + Shift + ;Enter the current time
Ctrl + ;Enter the current date
Ctrl + TabMove to next worksheet.
Ctrl + Shift + !Format number in comma format.
Ctrl + Shift + $Format number in currency format.
Ctrl + Shift + #Format number in date format.
Ctrl + Shift + %Format number in percentage format.
Ctrl + Shift + ^Format number in scientific format.
Ctrl + Arrow keyMove to next section of text.

Windows Tips

Comments

Leave a comment of your own

If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.





Powered by Movable Type 3.2 Some rights reserved © 2000-2004

Site feeds. Hofully valid XHTML and CSS. No kitties were harmed in the making of this website.