Excel-Tip-of-the-Month-Oct-09 by xiuliliaofz

VIEWS: 14 PAGES: 16

									Oct-09


           MENU    Click on the Link:


         1 Space Bar Short Cuts

         2 Replace errors

         3 Change Font Size

         4 Understanding Date & Time

         5 Compare Two Excel Files

         6 SUMIF
                                        Use this button to go
         If you wish to subscribe - email me to
          isaac.gottlieb@gmail.com

                      Send me your questions!
                      This is how I generate
                      ideas for the tips




e this button to go   Back to Main Menu
      To select a column or a row

      Try
Try   CTRL+Space Bar
      for row
      Or
      SHIFT+Space Bar
      for column
Back to Main Menu
                                        How do I change all these error cells not using Replace
 2         4          6       10   33
 4         3 #NAME? #NAME?         33
 6         2          8       10   33   1. Use CTRL+G or F5 (or EDIT>GO TO)
 8         3        11        14   33
10         2        12        14   33   2. You will se the Go To Menu (see figure 2)
12 #DIV/0!    #DIV/0!   #DIV/0!    33
14         3        17   #N/A      33   3. Select Formulas and Errors (see figure 3)
16         4        20        24   33
18         3        21        24   33   You selected all cells where the formula returned an error..
20         2        22        24   33   Type 0 or anything else you want.
22 w         #VALUE!          21   33   Press Ctrl+Enter.
24 w         #VALUE! Hello         33   See the results
26 #REF!      #REF!     #REF!      33




                         2                                           3
using Replace




eturned an error..




                     Back to Main Menu
                   Change Font size
Change Font size   To change the font size in Word or PowerPoint - once you
                   select the text you can use CTRL+] for increasing the size
                   and CTRL+[ for decreasing it

                   For Excel try
                   CTRL+SHIPT+P
                   In Excel 2003 or older versions It will position you on the
                   Font-Size cell on the menu. All you have to do is use the
                   arrow keys to increase or decrease your font size
                   In Excel 2007 it will open the format cell menu on the font
                   size menu
Back to Main Menu
Formatting Dates and Time
                       Symbol
                    m
                    mm
                    mmm
                    mmmm
                    d
                    dd
                    ddd
                    dddd
                    yy
                    yyyy
                    h
                    hh
                    s
                    ss
                    [ ] am PM
                    AM
                    pm A a P p
                    \
                    . (period)
                    "text"
                    Examples
                      Category
                    Date

                   Time
                                  Meaning
Displays
month or minutes as a number without a leading 0
month or minutes as a number with a leading 0
month as abbreviated text (Jan, Feb, Mar, and so on)
month as text (January, February, March, and so on)
day of the month as a number without a leading 0
day of the month as a number with a leading 0
day of the week as abbreviated text (Sun, Mon, Tue, and so on)
day of the week as text (Sunday, Monday, Tuesday, and so on)
year with two digits
year with four digits
hour without a leading 0
hour with a leading 0
seconds without a leading 0
seconds with a leading 0 minutes, or seconds place holders, displays hours
When surrounding hours,
greater than 24 or minutes and seconds greater than 60
Uses a 12-hour clock, displaying AM or PM as specified
Forces display of the following character
The decimal point.
Text within the quotes.

                                   Format                                       Value
m/d/yy                                                                             40409
d-mmm-yy                                                                           40409
h:mm AM/PM                                                                   0.754166667
[h]:mm:ss                                                                    2.792673611
                          Back to
                Back to Main Menu Main Menu


                         Back to Main Menu




Displayed As
    8/19/2010
   19-Aug-10
     6:06 PM
     67:01:27
ain Menu


ain Menu
Dear Isaac: Do you know by chance whether there is a
function in excel to compare two different excel files and
highlight the differences between two excel files. Thanks.

Excel does not have a function to do it.
You can download a program that could do it for you:

1. http://www.formulasoft.com/xlsc.html
2. http://www.synkronizer.com/
3. http://www.4tops.com/compare_excel_files.htm
4. http://www.download3k.com/Business-Finance/Office
Suites/Download-Excel-Compare-Two-Files-Find-Differences
Software.html
whether there is a
 ent excel files and
 excel files. Thanks.


 d do it for you:          Back to Main Menu


 xcel_files.htm
      Finance/Office-
            Differences-
-6      2        3       -10     9
 0     -1        4         4     0
 2     -1      -10         5    -7
-5     -8        9         2     1   You can sum the positive numb
-6      6       -8        -2    -9   range by using the SUMIF funct
-7      1        6         9     2
-5     -8        8        -2     3
 6     -6       -6        -1     5                   =SUMIF(data_
-1      4        3         1    -9
 3     -8       -3        -6     4
 3      8       -5        -9     1   To sum just the negative numbe
-3      4        8        -9     3
-4     -3        3         3     5                    =SUMIF(data_
 4      7       -1         5    -6
 8      1        5        -2    -6
 2     -7       -1        -4    -4


      172 =SUMIF(A1:E16,">0")
     -199 =SUMIF(A1:E16,"<0")


                                                 Back to Main Menu
sum the positive numbers in a
using the SUMIF function.

        =SUMIF(data_range,">0")

ust the negative numbers use...
         =SUMIF(data_range,"<0")




   Back to Main Menu

								
To top