All articles with 'Microsoft Excel Formulas' Tag

Here is a fun way to edit formula ranges. Just select the formula, press F2 and when you see the borders around the ranges used in that formula start dragging / resizing these borders to edit the formula range.

In this installment of spreadcheats we will learn how to understand excel formula errors and fix them. The #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE! errors and how to fix them.

Do you know that it is not so straight forward to use credit card numbers in excel. Yes, excel uses a precision of 15 digits and thus when you enter a credit card number (16 digits) it is converted in to scientific format and the details are lost. Read on to learn the work around.

In excel conditional formatting basics article, we have learned the basics of excel conditional formatting. In this article we will learn how to highlight top 5 or 10 values in a list using excel as shown aside. We will use LARGE excel formula and conditional formatting to do this.

Good news!!! We have upgraded the excel formula help page to a new, more easily accessible and better laid out page. Find out more…

Do you know What is excel conditional formatting? Learn the basics, few examples and see how you can use it in day to day work in this installment of spreadcheats.

Today we will learn a little trick to compare 1 value with a set of values, For eg. our sales vs. competitor sales. We will learn how to create a chart like this.

Hurray, PHD blog crossed 2000 RSS Subscriber base. This is a very significant milestone for me.

To celebrate this occasion we have a mega post: 100 Excel & Charting Tips, Tricks and Resources for you.

Here is a list of formulas I wish MS Excel had. Alas, we need to build some work-arounds to solve them though.

Everyweek PHD shares some of the Excel links to help you learn better and become productive at using Excel. This weeks edition will help you with printing, creating named ranges, finding excel version.

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate timestamps.

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.

One of the most common comments we get here is “how does xxx formula works?” Even though there is vast documentation on excel formulas including the built-in help in MS Office, often it is difficult for us to learn a formula quickly and use it in a snap. So to help new users of excel […]

Often when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you. Here is an excel formula that I use: =value(left(substitute(rand(),”0.”,””),10)) try these 2 instead (thanks to John) =RAND()*(9999999999-1000000000)+1000000000 =RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007] Even though it works most of the time […]

Reader Joann asks through email: Take the number 117490. I need to break it down5 times: 11749 1174 117 11 1 You can use FLOOR() function to get the desired result, like this: =FLOOR(your-number/10^digit-no,1) See the image below: