Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.
This article looks at 4 advanced techniques for summing values including;
Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.
This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.
In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.
A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.
This article looks at four techniques for separating text. Use the links below to jump to a specific technique.
A common question from Excel analysts and enthusiasts on my courses is to count the number of unique entries in a list.
This post looks at using a formula to calculate this distinct count.
Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.
At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.
The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.
Hyperlinks can be used to link to other sheets, webpages or other files such as PDF’s from your Excel spreadsheet. If you are creating hyperlinks for many records though this will take a long time to set them up.
Excel provides a HYPERLINK function for creating hyperlinks in our spreadsheets. The real power behind this function is that it can be used to create dynamic hyperlinks.
We can create conditional hyperlinks by nesting them within an IF function, or create hyperlinks that can find the address to link to themselves by embedding Lookup or Text functions within them. This helps us to create automated and error resistant spreadsheets.
Do you want to advance your skills by learning Excel? Performing date calculations can sometimes be troublesome. Trying to calculate the number of working days between two dates, or automatically finding the date in two months’ time is not as straight forward as formulas with general numbering can be.
Fortunately Excel has a full repertoire of fantastic date functions. Here are five of the best.