It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA. The best part about it, is you don’t need to learn or use any code to do any of it....

But can you calculate this in Excel? Yes, you can! Excel can be a great tool for a statistician when you know how to use it. In this post, I’ll show you 3 ways to calculate the correlation coefficient in Excel. Video Tutorial What is a Correlation Coeffecient? The correlation coefficient is also known as the Pearson Correlation Coefficient and it is a measurement of how related two variables are....

The great news is there a lot of ways to get this information into Excel. In this post, we’re going to look at 5 ways to get either the current date or current time into our workbook. Video Tutorial Keyboard Shortcuts Excel has two great keyboard shortcuts we can use to get either the date or time. These are both quick and easy ways to enter the current date or time into our Excel workbooks....

This post is going to show you how to add the accounting format to your numbers in Excel! Excel offers users a variety of number formatting options. This is because there are many types of numbers such as dates, percentages, or currencies. Different number formatting allows you to present the data appropriately. The accounting format is appropriate to use with currency values and has several great features that make comparing numbers super easy....

For example, it could contain names and addresses of customers or employees, but this all ends up as a continuous text string in one column of the worksheet, instead of being separated out into individual columns e.g. name, street, city. You can split the data by using a common delimiter character. A delimiter character is usually a comma, tab, space, or semi-colon. This character separates each chunk of data within the text string....

Go to the File tab. Select Options from the File menu. This will open up the Excel Options menu where you can find all of Excel’s many different settings options. The left hand side of the dialog box contains tabs to organize the various options into different categories and the right hand side is where you can adjust the settings. This is a valuable tip in itself, but we will explore some of the more useful options later on....

While the number in the back end would still remain the same, Excel changes the way it shows you that number. Sometimes that’s what you want, and sometimes you may want to convert the scientific notation back to regular numbers. In this short tutorial, I’m going to show you a couple of ways to convert numbers that are shown in the scientific notation back into being shown as regular numbers....

This could especially be useful in project planning, where some days could be for a specific task (such as having a project meeting or sending the progress report), or where you need to know what days the working days are and what days are the weekends. In this tutorial, I will show you a couple of ways you can use to convert dates into the day of the week and get its name in Excel....

In an attempt to make Excel a smart and more user-friendly tool, there are some in-built features that end up being a little bit of an irritation. Working with decimals, you will likely face such an issue. Let me explain! If you type 10.00 in a cell in Excel, it would automatically convert this to 10 by removing the zeros after the decimal. While this is a smart thing to do, sometimes, you may want all your numbers to show a specific number of digits after the decimal, even if these are zeros....

And many times, you will need to copy and paste an entire column (or multiple columns) in Excel. It could be a copy-paste in the same worksheet, or in any other worksheet or workbook. In this tutorial, I will cover everything that you need to know about copy-pasting columns in Excel. There there are multiple ways to do it. You can choose to copy and paste an entire column or multiple columns as is, or only copy the values or formatting, or formulas from a column....

REPT(“?”,N) will return a text string made of exactly N question marks. In our example REPT(“?”,2) returns “??“. COUNTIF(Range,”??”) will then count all the cells in the Range that contain exactly 2 characters because of the two “?” wildcard characters in the COUNTIF criteria. In our example Range has 3 cells which contain exactly 2 characters, so COUNTIF(Range,”??”) returns 3.

We then use LEFT(Email,PeriodLocation-1) to get the first PeriodLocation-1 left most characters from our email address. We subtract 1 from the character location of the first period since we only want the characters before the period. In our example this is LEFT(“jim.smith@breadmakers.com”,3) which will return jim all in lower case since our email address was all in lower case. We then use PROPER(FirstName) to return the first name in its proper case with the first character capitalized....

In our example MIN(Range) returns the value 0 since 0 is the minimum value in the range of values {1;0;8;6;9;7;9;6}. MATCH(0,Range,0) looks for a 0 in the Range and will return the position of the first 0 it finds. The 0 at the end of the formula is a predefined Excel parameter that tells the MATCH function to find an exact match in the Range. In our example MATCH(0,{1;0;8;6;9;7;9;6},0) returns the value 2 since the first 0 in {1;0;8;6;9;7;9;6} is in the 2nd position....

In that post, I showed you a way to randomly select from a list using the RANDBETWEEN function. This method produced an equal chance of selecting each item in the list. But what if we want to weight those probabilities of selection so that some items are more frequently selected than others? With this method we can create the probability distribution of an item being selected. In this example, we are going to select from a list with 4 items....

In Excel. you can quickly change the case of the text in a cell (to lower case, upper case, or proper case) using text functions. Below is an example of each type of case: Excel PROPER Function – Overview PROPER function is one of the many text functions in Excel. What Does it Do? It takes a string as the input and returns a string where the first letter of all the words has been capitalized and all the remaining characters are in lower case....

If your work involves applying the same type of formatting to data sets, using Format Painter in Excel will save you a lot of time. What is Excel Format Painter? Excel Format Painter is a nifty tool that allows you to copy formatting from a range of cells and paste it somewhere else in the worksheet (or other worksheets/workbooks). Imagine this! You get plain ugly data from a colleague, and you spend the next few minutes applying some formatting to make it beautiful....

And in most of the cases, you need these groups to be random. Today, I am sharing a random group generator template that will make it super easy for you to create a group of students/participants. All you need is the list of students or participants and specify how many groups you want to create. Random Group Generator Template Here is a demo of how this random group generator (or random team generator) template works:...

They had started out using Excel to quote for all their construction jobs. Excel is an amazingly flexible tool and it can definitely be used as a quote calculator. The problem with using Excel is it doesn’t scale well. As they added more project managers to the organization and price lists changed, they were having difficulty with version control. Were their project managers using the most recent version of the template?...

In almost all of the situations, you don’t need to know what this is, and the default settings in Excel would be enough for you. But since you are here reading this article, I’m assuming that you either have a need to know what is the difference between the R1C1 and A1 reference notations, or you’re naturally curious. In this article, I will tell you everything you need to know about R1C1 and A1 reference notation in Excel, and the Pros and Cons of each....

Just like the LOWER function, there is a similar inbuilt function in Excel VBA – LCASE The VBA LCASE function takes a string as the input and converts it into a lower case string. Syntax of VBA LCASE Function Below is the syntax of the VBA LCase function ‘String’ is the text that you want to convert to lower case. You can use a text string, a range reference that contains the text string, or a variable that has the text string....