Sum 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 readingSelecting Next Cells In A Different Order
If you have ever used Excel, you probably know already that you can move from your current cell to the one to the right, or below by using the tab or return keys. So what if you want to select the next cell, but the next cell is not in the normal order? For example, […]
Continue readingMultiplication Table Challenge Solution
The Challenge In the last blog post I set a challenge for you. Create a table similar to the one below, in cell B2, create a formula that can be copied to all other cells in the table. The result of this will be a multiplication table. The Solution The solution in theory is […]
Continue readingMultiplication Table Challenge
Feeling up to a challenge? Here is a challenge for you to complete in Excel. Can you come up with a formula in cell B2 below that can be copied down and across the rest of the table below? From cell B1 across to K1, list the numbers 1 to 10 From cell A2 down […]
Continue readingCreate A Planner In Excel
It’s that time of year again when I start receiving requests for help with creating planners for the new year. These planners are usually for teams or groups to organise holiday schedules and rosters. This year I created a reusable planner that can be used in future years and also includes most of the features […]
Continue reading3D Sum
In Excel, a 3D Reference refers to the same cell or range in multiple worksheets. The 3D Sum allows you to sum the same cells in multiple worksheets. To demonstrate this, we have 4 worksheets and in Cell B1 of each sheet, there is a sales value that we want to get the total of. […]
Continue readingUPPER, lower & Proper in Excel
In an earlier post I discussed using len, left and right and mid. Now I will look at more functions that fall under “Text” in Excel – upper, lower and proper. These 3 functions allow you to change the case of text in your files. I usually use these functions on files that have been […]
Continue readingSort Left To Right In Excel
If you’ve covered even the basics of Excel, there is a good chance that at some point you have sorted at least 1 column of data in ascending or descending order. Sorting up and down is well covered in classes, books, tutorials and with a big button in Excel ribbons that says “Sort”, but […]
Continue reading