Excel Hints has continued to grow over the last year and since we are approaching 100 subscribers (I’ll definitely have another post when we pass the milestone), I have decided to add a new Excel Hints Forum to the site. 

I believe if we can get users to register and begin posting, this can become an invaluable tool to getting everyone’s questions answered as quickly as possible.  With my full time job, I don’t always have time to answer everyone’s questions I get through email, so I think this is the perfect solution to that.  I’ll answer as many questions as I can through the forum and hopefully we can get other’s to help out too if they know the answer.
Continue Reading »

I have been using this quick trick lately to check for duplicates in a column of values.  Let’s say you have a list of a large group of numbers (characters work as well) and want to see if you have more than one instance of any of the numbers.  To check for that, one simple was is to use the CountIf formula.

I have already shown you how to use the formula in the SumIf and CountIf Foumulas post. But here I just wanted to mention this is an example where you can use it.

Let’s say we have a column, say column D, with 100 values that you want to confirm do not have any duplicates. To check this, in a column next to the data you are dealing with you can add this formula:
Continue Reading »

The substitute formula is an relatively simple formula that can come in handy in many situations.  I like to use this formula the most when a longer text field needs just a portion of it changed, without having to retype the entire cell value.  Let’s go ahead and take a look at the substitute formula:

=SUBSTITUTE(Text, Old_text, New_text, Instance_num)

Looking at each of the criteria of the field, we’ll see how this formula is formed.  Text is the string or cell reference of the string you want to change.  Old_text is the value inside the Text string that needs changed. New_text  is the value you want to change to the Old_text  value to.  And finally Instance_num is only specified if you only want to change a certain occurence of the the Old_text.  If you don’t specify and Instance_num, all instances of Old_text will be replace. 

Alright, let’s go ahead and take a look at some examples of how the Substitute Formula works.

EXAMPLES

Example 1: =substitute(”Let’s have fun.”, “fun”, “dinner”) will return “Let’s have dinner.”, since “fun” is replaced by “dinner”.

Continue Reading »

I have been having numerous problems with the site lately and am finally almost finished getting everything back to working order.  For a long time I was unable to upgrade my version of WordPress without it crashing, but that problem is now fixed.  I have put back together all the category sections (I’m still working on the tips menu that was there before, on the right side of the site).  Overall I’m pleased with the new version of Wordpress and believe it will definitely help with to streamline updates and new posts to the site. 

Another sidenote, if you left a comment over the last several months, it most likely did not show up on the site.  I have since fixed that problem as well and approved your comment.  I will be responding to all those comments shortly. 

Thanks again to everyone’s patience and for sticking with Excel Hints!

Excel Hints

Excel Hints has been having trouble with the SQL databases used by WordPress recently.  It appears we have worked out most of the problems. I would like to apologize to all the readers of the site for the down time and thank you for your continued support.  New postings will continue soon. If you want to contact me, please use our contact form.

Thanks You,
John
ExcelHints.com

Lately all the updates I have been adding to excel hints have been related to the formulas that are incorporated into Microsoft Excel.  While I still probably have hundreds of formulas that need added to this site, I am going to begin adding tips on how to use the Macro capabilities for Excel.

Macros in Microsoft Excel are written in Visual Basic for Applications (or VBA), which is just a program to provides you with the ability to a little more functionality to your spreadsheets.  Macros are an excellent choice to use for any repetitive tasks you need to accomplish, and the examples I will provide will make that obvious.  As with the formulas added to the site, we will begin with some basic functions and build upon them.

Anyone that has any suggestions on something that they would like to see on the site or suggestions on how I can make the site better, please contact me using the contact form.

Whether you are trying to buy a new house, looking at buying a new car or applying for MORE student loans, you have no doubt wondered what your monthly payment would be. Excel provides an easy to use formula in which you can enter the loan information, and it will calculate the monthly payment for you.  Once you learn to use it, it’s actually very simple.  So let’s go ahead and take a look at the PMT Formula: Continue Reading »