It’s a common scenario to try and explain the change in financial results from year to year or quarter to quarter. But if your financials are in a Power BI dashboard, then there is no way to add commentary to explain any change in results. We can write data back to our source with PowerApps though! In this post, we’re going to take a look at how we can integrate Power BI and PowerApps....
It can be found in the Data tab in the Get & Transform Data section of the ribbon. It’s very powerful and also very easy to use and the query editor has a super intuitive user interface for an Excel user. Many transformation steps can be easily performed from the power query editor ribbon and you don’t need to know any code to clean and shape your data. Behind the scenes of the user friendly editor, Excel is translating each step in your transformation process from those ribbon commands into the power query M code language....
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....
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....
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?...
While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. For the ultimate in Excel efficiency, you should combine both keyboard and mouse. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks. Video Tutorial Mouse Shortcuts For The Window Close Multiple Workbooks At Once If you’ve got a ton of Excel workbooks open and you want to close them all, then it can be a pain go through and close each workbook individually....
Strikethrough font can be a good great way to visually indicate something has been deleted, removed or is no longer relevant without actually removing it from your text. This formatting option is also available in Excel, but unfortunately it’s not available directly from the ribbon commands. However it can be accessed in quite a few ways. This post will show you all the ways you can add strikethrough font to your Excel workbooks....
Excel is just a really big and flexible calculator, so it makes sense that some people use it for math problems. The factorial comes up a lot in statistics when calculating permutations and combinations of events. So it’s likely one you’ll need to calculate quite a bit when doing any statistical work in Excel. This post will show you all the ways you can find the factorial of an integer number in Excel....
MOD(Range,2)=0 will create an array of Boolean values where TRUE will mean the number is even and FALSE will mean the number is not even. We then convert these TRUE and FALSE values to 1 and 0 values by multiplying the array by 1. This will give us the count of the number of even numbers in the range. In our example MOD({1.36;-2;2;9;10;7;1.33;8},2)=0 will result in the following Boolean array....
In our example ISBLANK(Range) creates the following array. MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},0) then looks for the first exact match to a TRUE value within this array. This returns the value 3 since the first TRUE value is in the 3rd position and this is our first blank cell in the range.
This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function....
Syntax IFNA(Expression, Value if #N/A!) Expression (required) – This is part of the formula that is checked for an #N/A error.Value if #N/A! (required) – This is the value that is returned if the expression results in an #N/A! error. Example In this example we use the IFNA function with a VLOOKUP function. If the VLOOKUP function results in an #N/A! error (ie the lookup value was not found), then the IFNA function will return the text “Item not found”....
