- Home
- Excel Lessons
Excel Format dates to include st, nd,rd and th
In an eBook “Date Functions”, I showed you a formula that would include st, nd, rd and th to a day number, so 14 November would display as 14th November. I have since been asked by some people if it is possible to format dates to include st, nd, rd and th. The answer is yes, you […]
Continue readingSum By Color in Excel
With improvements to the last few versions of Excel I see people using colors in cells more, the ability to filter by color adds to the benefit of using fill colors. However, the increasing use of colors has led to the increasing number of requests for help I receive to sum by color in Excel. […]
Continue readingExcels Scenario Manager
This post looks at Excel’s Scenario Manager function in 4 main sections. Setting up scenarios with the Scenario Manager Switching between scenarios Creating a summary report comparing scenarios Overcome one of the downfalls of scenario reports – making sense of “changing cells” If you are familiar with the scenario manager summary reports already but want […]
Continue readingINDEX MATCH An Alternative To VLookUp
Having worked with and trained a range of people from different departments and businesses, I have seen that the VLookUp is the function that I am most asked for help with or training on. Here I would like to show you an alternative to the VLookUp which is INDEX MATCH. INDEX MATCH solves some of […]
Continue readingVLookUp To Retrieve data relating to the second or third match
If you have ever used VLookUps before you may have noticed that the formula only retrieves data relating to the first match it finds. Did you ever need a VLookUp to retrieve data relating to the second or third match? In an earlier post called VLookUp with multiple values and I showed you how to retrieve […]
Continue readingCOUNTIFS – COUNTIF FOR MULTIPLE CRITERIA
In my last post I talked about SUMIFS – SUMIF FOR MULTIPLE CRITERIA, this time I’m going to talk about COUNTIFS, which you may have guessed is a way to carry out a COUNTIF for multiple criteria. Before continuing, you should be comfortable with the COUNTIF function, click here to read my post on COUNTIF. […]
Continue readingSUMIFS – SUMIF FOR MULTIPLE CRITERIA
SUMIFS can be treated as a SUMIF for multiple criteria. Recently I was asked for help on a file where SUMIF formula were used to add cells specified by certain criteria. The user wanted to create a SUMIF for multiple criteria though, and that is where SUMIFS came into it. Click here to read my […]
Continue readingSum Odd or Even Numbers only in Excel
Pardon the pun, but it may seem like an ODD request to want to know how to sum odd or even numbers only in Excel, but it is still a question asked. Since being asked for help on this, I have started using this formula myself recently where I need to sum odd numbers and […]
Continue readingCustom Sort
So, you can sort from A-Z, Z-A, smallest to largest number and so on, but can you create your own custom sort? Let’s base our custom sort on this data: Before going onto the custom sort, let’s very quickly look at “normal” sorting. If you are comfortable with the normal sorting, you can skip […]
Continue readingAvoid #REF! in Excel by knowing your Dependents and Precedents
Have you ever opened a file in Excel and saw the dreaded #REF! where a formula result should be. I usually see people fixing this formula by removing the section with the error, or guessing what range should be entered instead of the #REF!. This may work a lot of the time, but it might […]
Continue readingHow To Auto Fill Sequential Letters in Excel Video
To date, the blog post “How to auto fill sequential letters in Excel” is my most searched post (click here to read how to auto fill sequential letters in excel). For this reason I thought it was worthwhile creating a video breaking down the formula. As well as the formula itself, I also often get […]
Continue readingSimplify IF AND OR eBook
My second eBook is almost ready! It is now being reviewed and will be available soon to all subscribers. This time around we look at IF AND OR functions – these are areas where I am frequently being asked for help to clarify.
Continue readingUsing Pivot Charts For Better Analysis
Using Pivot Charts when analyzing data can show you more in one image and quicker than standard charts where you may have to create multiple charts. In this image, there are two standard charts showing the sales by person and the sales by region. But to the right we have a Pivot Chart which shows […]
Continue readingVLookup with multiple values
So, you can use a VLookUp to retrieve a piece of information. You can use SumIf to total a range based on criteria and you can use CountIf to count how many times criteria appears. What do you do though when you have to create a VLookUp with multiple values? The VLookUp will run through […]
Continue readingEXCEL SUBSTITUTE FUNCTION
I have been coming across more and more cases where people have problems with lookup formulas because databases do not have matching information. The most common type of incident I come across are where one database contains a name like “Adam O’Reilly” and another database has the name as “Adam OReilly”, with no apostrophe. So […]
Continue reading