My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
The Excel TIME function combines separate hour, minute and second values and converts them into a time serial number. If the cell format was General before the formula is entered, the result is formatted as a date. Syntax: =TIME(hour, minute, second) hour: A number from 0 (zero) to 32767 representing the hour. Any value greater …
With a family of 4 fussy eaters to feed (well actually, 3 fussy eaters and myself) I do a lot of research on the internet for new recipes. The problem is I often find great recipes but they’re in imperial measurements and I have to convert them. Actually, it’s not that big a problem ‘cause …
The other day I was in an Excel forum helping answer some questions. I like to help, because….well, I like to help, plus I like a challenge and it gives me ideas for things I can share with you. Anyway, I came across this tip (posted by someone else) and I had to read it …
Excel Database functions have been around for donkey's years so it's surprising that not many people know about them. They work great with tabular data and Excel Tables. They're not only easier to use and more powerful than more popular equivalent functions like SUMIFS, COUNTIFS etc., but they can get around some of their limitations. …
Wildcards *?~....what am I talking about? And no, it's not an expletive. Let’s start with an example. COUNTIF using Wildcards What say you wanted to count the number of cells containing the word ‘apple’ in this table. You could simply use a wildcard (an asterisk, *, is a wildcard in Excel) in your COUNTIF formula …
In Excel there are two almost identical functions; SEARCH and FIND. The SEARCH and FIND functions allow you to find the location of text within a string of text. They’re identical because they perform the same function except the FIND function is case sensitive, whereas the SEARCH function is not, and SEARCH allows the use …
Did you know you can analyse data from an Access database in an Excel Pivot Table without importing the data first? If you use Access then this is a great productivity booster because once you connect to your Access query you simply refresh the data connection and your Pivot Table updates, as do any charts …
So, you’ve analysed your data and plotted your chart but the axis labels are all skewey (technical term). Let’s look at our options to make this better: Chart Axis Alignment We could use the alignment tools. Right-click axis > Format Axis > Alignment. But before you waste time doing this there is a better way. …
One of the Excel questions I get asked often is; how do I add a secondary axis to my chart? It’s actually quite easy but there is a trick to it. Excel Secondary Axis Trick Step 1 Let's take this data. Now, the first thing you want to do is simply insert your chart. Insert …
If you work in large Excel files, Word documents or even e-mail, you may find it takes a long time to navigate and find the location you’re after. Hyperlinks are like a time machine in that they can take you there in an instant (but unfortunately they can’t take you back in time), and the …
A Histogram, also known as a frequency distribution, is a chart that illustrates the distribution of values that fall into groups. Since my 5 year old is big into his football (soccer) we’ll take goals scored as an example…even though in 5 year old's football matches you’re not supposed to count the goals scored! Below …
Each time I write a tutorial I use the Google Keyword tool to find search phrases that are relevant. I use these phrases as tags on my post to help you find it easier in Google and other search engines. The Google keyword tool spits out a list of phrases which I then export into …
If you just want a quick count of the number of items in a list or a range of cells you can simply select the range (with your mouse), and look at the Status Bar at the bottom right of your Excel window. This will count all cells that are NOT blank in your selected …
If you find yourself opening the same workbooks each day when you start work, or when you do a particular job then you can save time by grouping these workbooks as a Workspace. It’s dead easy to do. Note: the Workspace feature was discontinued in Excel 2013 onwards. This only applies to Excel 2010 and …
If you ever work with large tables of data and you want to insert a VLOOKUP formula that dynamically updates to the next column as you copy it across, then the VLOOKUP with the COLUMNS function is what you need. That is; the col_index_num part of the VLOOKUP function dynamically updates as you copy it …
Knowing Excel array formulas will catapult you to ‘Guru Status’ in your office and I'll do my best to make this painless, but you might need to get a brainbooster (a fruit or veg snack given to the 5 year olds at my son's school at 10am each morning when they're starting to lose focus). …
Importing data to Excel from other programs and sources enables you to further analyse and generate reports or dashboards from what is often 1000's of rows of data. On the Data tab of the ribbon in the section Get External Data there are four options: From Access – a Microsoft Access Database From Web – …
If you import data from another program it often comes in with additional spaces either at the beginning, end or sometimes between words. Thankfully the Excel TRIM function will remove the excess spaces from text, except for single spaces between words. How to use the Excel TRIM function It’s a simple function =TRIM(text) Where ‘text’ …
Sometimes when you import text from another source it may not be formatted in the case you want. For example it may be ALL IN CAPITALS, all lower case, or a combination of the two. There are a few functions in Excel that we can use to quickly fix the formatting of text, namely UPPER, …
I often create Excel workbooks that contain a series of sheets for each region (or product, month, or sales person) with a summary sheet at the front that gives me a total of all the sheets. But building workbooks like this can be onerous so I’d like to share with you a few tips for …
In Australia we did away with 1 and 2 cent coins years ago. So all prices, where paid in cash, are rounded down to the nearest value divisible by 5 cents. In Excel we can use the FLOOR function to calculate this value. For example: Say our price is $4.32 and we need to round …
The Excel CHOOSE function is not much use on its own, but when you nest it with other functions it can make quite a difference to their capabilities. First let’s look at the basics of the CHOOSE function. Excel CHOOSE Function syntax: =CHOOSE(index_num, value1, value2, value3…..up to 254 values) With an example: =CHOOSE(3,"Blue","Green","Orange") Translated the …
The MIN, MAX, SMALL and LARGE functions in Excel are fairly straight forward, but I’d like to show you a couple of tricks that won’t be obvious from reading the Excel help files. First of all the syntax for MIN and MAX functions: =MIN(number1, [number2],….) =MAX(number1, [number2],….) The ‘number’ can be keyed directly into the …
Updated April 27th 2023 Standard Deviation in Excel is super easy to calculate, and we have separate functions for the standard deviation of a sample or a population. I'm not going to go into too much detail on what the standard deviation measures, because if you’re reading this you probably already know that it is …
In Excel 2007 the graphics features are vastly improved. Not only have you got some instant designer quality flow charts (see image below), with the new and improved Shapes you can now apply effects like you find in Photoshop. Excel SmartArt I’m not going to go into a lot of detail with SmartArt. Other than …
Excel’s RAND and RANDBETWEEN functions aren’t very well known, but they can be quite handy. RAND Function RAND is a simple function that returns a random number between 0 and 1. It is entered =RAND() That’s it…simple. RANDBETWEEN Function RANDBETWEEN is slightly more complicated. =RANDBETWEEN(bottom,top) Where ‘bottom’ is the lowest number you want returned and …
Ever had a spare $10,000 to put in a term deposit? If you did you might wonder what it would be worth at the end of 2 years at an interest rate of 6% compounded monthly. It’s not as simple as =$10,000 x (1+.06 x 2)…although this will get you close. But if you’re a …
Our economy has been hard hit and we’ve got less spare time than ever before. So in this business climate it’s a natural reaction to cut costs, and we should. For small businesses the training budget will be one of the first to go since it’s seen as an overhead, but for corporate organisations training …
You’re not likely to hear about Excel’s Camera tool on a training course and you certainly won’t find it on the ribbon. It is one of those insider tips that are hidden away in the toolbar options. What is Excel’s Camera Tool The camera tool takes a snapshot of a range of cells from somewhere …
In my last tutorial I showed you Excel's T function, today we’re looking at the other one-letter function; the Excel N function. Funnily enough the N function was only provided for compatibility with other spreadsheet programs, but it does have one handy use if compatibility is not your raison d’etre. The syntax is: =N(value) Depending …
