- Home
- Excel Lessons
Wildcards in Formula
A wildcard character is a character that can be used as a substitute for another character or characters. I use wildcards in formula mostly in reference or look-up formulas like COUNTIF or SUMIF for example. The wildcards can be used to look up words beginning with or ending with criteria. Note : It can […]
Continue readingSUMPRODUCT
Continuing the series of posts about useful, but sometimes underused Excel formulas, this post will give you an introduction to SUMPRODUCT. SUMPRODUCT is one formula that pretty much combines two together. SUM : When you add two or more numbers together, the result of these is the “SUM” of them. PRODUCT : When you multiply […]
Continue readingHow to get filename in Excel
As a follow on from the last post, I was asked how to get filename in Excel using similar formulas. This can be done with the formula below. =MID(CELL(“filename”),(SEARCH(“[“,CELL(“filename”))+1),(SEARCH(“]”,CELL(“filename”))-6-(SEARCH(“[“,CELL(“filename”))))) Really simple isn’t it? I bet you were wondering how you didn’t figure it out yourself!! This combines 3 formulas, MID, CELL, and SEARCH. I’ll […]
Continue readingTHE LEN LEFT RIGHT MID FORMULAS IN EXCEL
The LEN LEFT RIGHT MID Formula are 4 Excel formula that can be really useful. They might not be used that often, but when you need them, they can save you a lot of time. I will go through each of the 4 formulas and then give examples of where they can be combined. For […]
Continue readingCOUNTIF
The COUNTIF formula is another very useful formula in Excel for analysing data and getting more information from it. If you have not already read these posts, please take a few minutes to read them now. The post on brackets and commas will show you how to break down longer formulas making them easier to […]
Continue readingSUMIF
The SUMIF formula is a really useful formula for adding the values in cells based on set criteria. This post will build on what you have seen already in the IF Statements post and will use the example of game scores. To demonstrate this, we will use an example of a list of scores each […]
Continue readingHow to auto fill sequential letters in Excel
This week I was asked how to auto-fill sequential letters in Excel. What do I mean by this? If you type 1 in a cell and drag it down Excel can populate the cells with sequential numbers (2, 3, 4, 5, 6, 7, 8, etc). A user needed a list of sequential letters (a, b, […]
Continue readingIF Statements
IF statements are usually thought relatively early in Excel courses soon after addition, subtraction, multiplication, etc. For this reason I guess a lot of people consider them to be “basic” formulas. However, you would probably be surprised by the number of questions I get about IF statements. Some people don’t fully understand them or find […]
Continue readingBrackets And Commas in Excel
I am regularly asked for help with Excel files where people don’t understand some of the formulas used. In some cases the person is just not familiar with the formulas but there are often times when the person understands the individual formulas but can’t understand what they see onscreen because several formulas are used together. […]
Continue readingWingdings
I receive a lot of requests for help with setting up files/charts (most often in Excel). Occasionally I think people try to challenge me on purpose by setting certain restrictions. In one case, I was given a table of weekly numbers and asked to visually highlight if one week was higher or lower than the […]
Continue readingThe Scroll Button
If you are using a mouse with your computer/laptop you may already be familiar with the left button and the right button, but do you use the middle button much? Talking to different people at different levels of computer skill, I was surprised by the number that did not use the middle button, or know […]
Continue readingWhen a shortcut’s not a shortcut
A shortcut is only a shortcut when it’s a shortcut. There you go, the shortest blog ever!! Want me to expand? My computers are set up with customized quick access tool-bars, quick launch bars and macros linked to shortcuts to help me work faster. I frequently keyboard shortcuts (Ctrl + C to copy for example) […]
Continue readingThe Alt Key Shortcuts
This tip is aimed at those of you who use keyboard shortcuts, or are interested in trying them out. My example below uses Excel 2010, but can be applied to other Office 2010 programs Whether they use them or not, a lot of people are at least aware of some of the basic “Ctrl+” shortcuts: […]
Continue readingQuick Access Toolbar
The Quick Access Toolbar (QAT) is a customizable toolbar that appears at the top of your screen in MS Office (Excel, Word, etc) versions 2007 and later. In older versions, it was possible to create your own toolbar. The toolbar allows you to add the commands that you use most frequently so that they always […]
Continue readingCombining Charts In Excel
One of the most common questions I get asked in relation to charts is how to combine two charts on one. The chart below shows actual sales numbers and target sales numbers. This is a common comparison of targets or expected numbers against actuals. The values can all be seen, the target numbers are blue […]
Continue reading