Thanks to antville.org
Excel formula tip

While you enter a formula in a cell, press CTRL+SHIFT+A to see the arguments in a formula. If you type =RATE, and then press CTRL+SHIFT+A, you can see all the arguments for that function--for example, =RATE(nper,pmt,pv,fv,type,guess).

... Link (0 comments) ... Comment


Excel Tips

Quickly Typing Dates into Cells

Typing a large amount of data into cells can be tiring, especially if it includes a series of dates. Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:

=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)

Type a full number in the cell and change it to a date by using the following formula:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/" &RIGHT(A1,2))

For example, type 122203, the result is 12/22/03.


Counting number of unique entries in a specified range

By using SUMPRODUCT along with COUNT to count only the number of unique entries in a specified range. The range can be all in one column or row, or may span multiple (adjoining) columns/rows.

The formula would be written as: =SUMPRODUCT((1/COUNTIF(A1:C5,A1:C5&""))) where A1:C5 is replaced with the range you want to apply the count to.


Retrieving an Item from a List that Meets Multiple Criteria

Problem:
Range A2:C7 contains a list of items and their corresponding colors and prices. We want to find the name of the first Blue item that costs less than $200.

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7 < D3)))}


Color numbers based on the value result in Microsoft Excel

There are two ways to color numbers according to criteria:

* Custom formatting for a number with criteria  
* Conditional formatting  

Using custom formats for numbers with criteria
Your options for coloring a number are not limited to displaying negative numbers red. You can color positive and/or negative numbers in any color you wish (do not get excited - the number of colors is limited to eight, and most are difficult to read). Do this by adding the name of the color to the number format in brackets.

Example: [BLUE] #,##0 ;RED

A positive number is displayed in blue; a negative number is displayed in red; and 0 is displayed in blue (since there is no third section, 0 receives the format of the positive number by default).

Add a condition to the formatting, and have each section be displayed in a different color: [BLUE] [>5000]#,##0 ;RED; #,##0


Indicators for the maximal and minimal values
Problem:
Creating a formula that will indicate the maximum and minimum values in List1 by returning ""Max Value"" or ""Min Value"" in the cell next to them.

Solution:
Enter the following IF statement, using the MAX and MIN functions, in column B: =IF(A2=MAX($A$2:$A$8),""Max Value"",IF(A2=MIN($A$2:$A$8),""Min Value"",""""))

... Link (0 comments) ... Comment


Difference in Months

=DATEDIF(C3,NOW(),"M")

The DATEDIF function calculates the difference between a starting date and an ending date. The "M" used in the formula indicates that you want the result in completed months.

... Link (0 comments) ... Comment


Saving Common Formulas in Excel

You will have to assign names to your formulas.

  1. Enter your formula as you normally would.
  2. Copy the formula. (Not the entire cell)
  3. Choose Name from the Insert menu, then choose Define.
  4. In the Names in Workbook box, enter the name you want assigned to this formula.
  5. Paste the formulae in the Refers To box.
  6. Make sure there are no dollar signs in the formula. If there are, select them and delete them. (This method of using formulas does not work well with absolute references.)

Now, whenever you want to use the formula, you simply enter an equal sign and the name you gave to the formula.

... Link (0 comments) ... Comment


Excel formulas

To apply data validation to a column which allows only unique items to be entered, highlight that column and select Data, Validation from the menu. Choose the custom option and enter the following formula (for column A):

=COUNTIF($A$1:A1,A1)=1

This array formula returns the number of unique items in a worksheet range.

=SUM(1/COUNTIF(Rng,Rng))

... Link (0 comments) ... Comment


Outlook Tip

You can add signature to all outgoing mails.

Tools - Options - Mail Format - Signatres...
Add you own signature line for e.g. name, email and contact number.

... Link (0 comments) ... Comment


E-mail tips

Spell Check
Here’s what you need to do to get Outlook's spell checker to kick in for every single mail that you send out.
Select Tools > Options and switch to the Spelling tab. Here, under Settings, tick the check box next to “Always check spelling before sending” and then click on OK.
Now the next time you your type out a mail and hit the Send button, the spell checker will point out any and every typo you have made.

Address Book Addendum
Double-click on an e-mail you have received from someone to open it. Right click on the sender's mail address (the From field). Next, select Add to Address Book.
To edit the person’s name you have just added, choose File > Address Book. Now select the name and click on File > Properties. Edit the name as per requirements and click on OK.

Bunching Fowards
If you want to forward several e-mail messages at the same time to someone, go to the folder containing the messages you'd wish to forward. Holding the Ctrl key down, click on each message you want to forward. Then, right-click on any message in your selection and pick Forward. A New Message window will appear with the messages attached (across the bottom of the window). Now simply complete your message and zap it off.

... Link (0 comments) ... Comment


Numbers in Thousands

Is there a number format that I can use that will display numbers in the thousands without actually using a formula? So, 20,000,000 in sales in a cell would actually be displayed as 20,000.

Select cell where you have typed 20,000,000
Choose Format - Cells and type the form #,##0, in the custom field. The number format #,##0, works because of the comma at the end.

... Link (0 comments) ... Comment


Sent to me?

You can set Microsoft Outlook to color-code all email addressed only to you blue, because those messages are more likely to be more important and require action from you.
To get this set up in Outlook, from the Tools menu choose Organize, and then in the "Using Colors" section hit the "Turn on" button next to "Show messages sent only to me in Blue."

... Link (0 comments) ... Comment


Hiding cells

Use a custom format for the cells whose content you want to hide. Follow these steps:

  1. Select the cell (or cells) you want to hide.
  2. Choose the Cells option from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is displayed. (Click here to see a related figure.)
  4. In the list of format categories, select Custom.
  5. In the Type box, enter three semicolons (;;;).

Now the information in the cell is not visible, nor will it print.

... Link (0 comments) ... Comment


Online for 6297 days
Last modified: 11/21/19 1:09 AM
Status
Youre not logged in ... Login
Menu
... Home
... Tags

Search
Calendar
November 2019
SunMonTueWedThuFriSat
12
3456789
10111213141516
17181920212223
24252627282930
October
Recent updates
Very useful I needed this
thank you! Between the house cleaning and the tow trucks...
by MarbD (11/21/19 1:09 AM)
Bucket list one of my
favorites sites www.drawinnovations.com
by Monica11 (11/20/19 9:04 PM)
Incredible post! This is
greatly amazing. | roofing contractors near me
by Danielperez123 (11/20/19 8:03 PM)
Nice Antville.org is a great
article to be discussed! Thanks. Medical Converting Company
by Danielperez123 (11/20/19 6:26 PM)
Hello, Thank
you soo much for this great code of java. I am trying...
by Martin Moolley (11/20/19 11:37 AM)
Crack software has ultra-current
Vulnerability Shield. Free download Eset brilliant security eight complete break Direct...
by theumar20 (11/19/19 10:33 PM)
This site provides free serial
keys and activation codes. https://serialkeysfree.org This site provides free serial/activation...
by ayatnoor (11/19/19 7:14 PM)
Nice ohh! This is a
great reference. Thanks for sharing. | phillips norelco beard trimmer
by Danielperez123 (11/19/19 7:12 PM)
Bucket list Nice to know
that there is a site like this who are providing...
by Mikmik (11/19/19 2:55 PM)
Microsoft Office 365 Product Key
List for Free Microsoft office 365 Product key Microsoft Office...
by emilyisa bellaamelia1 (11/19/19 1:31 AM)
Bucket list Thank for this
post . this really help me a lot. carpet cleaning...
by Monica11 (11/18/19 8:09 PM)
The backup worked effectively
and effeciently for me. Great post! click here
by brielleluna (11/18/19 7:36 PM)
WinX HD Video Converter Deluxe
Crack WinX HD Video Converter Deluxe 5.15.3 Crack Key...
by meci12 (11/17/19 4:03 PM)
Nice Same here. I used
the same product. Thanks! | junk yards near me
by Danielperez123 (11/15/19 4:12 PM)
tellthebell Nice share. But, am
getting an error whenever I setup . So, what should...
by zenvanik (11/12/19 9:22 AM)
great to learn Thanks I
was needing this. As someone who prefers tree service winnipeg...
by TheManofRight (11/11/19 11:26 PM)
Noted. Got to apply
this over our website. Tampa SEO
by brielleluna (11/11/19 5:22 PM)
Is this another kind of
AWS management console that was posted on the other...
by brielleluna (11/11/19 5:19 PM)
sitelinx great work פרסום
בטאבולה
by sitelinxblog (11/9/19 8:36 PM)
Amazing! Thanks for welcoming
us with your great information. Keep it up! https://thesoutherninstitute.com
by Danielperez123 (11/8/19 4:05 PM)
Great Good to know you
have also provided which file type should it be saved....
by brielleluna (11/7/19 5:35 PM)
I will try to
apply them. cheapest seo services
by louishill6427 (11/6/19 9:19 AM)
I use these amazon
products too. Credit Card Processing Companies
by louishill6427 (11/6/19 9:14 AM)
Thanks for sharing how
to convert private to public key. Outbound Lighting
by louishill6427 (11/6/19 9:07 AM)
Thanks for sharing the
link for bookmarklet. Cincinnati lot for sale
by louishill6427 (11/6/19 8:42 AM)

RSS feed

Made with Antville
Helma Object Publisher