Posts

Just a reminder, that in Excel 2010 and 2013 Power Query is an add-in and you can find out how to install it here. In Excel 2016 it comes pre-installed under the Data tab in the ribbon and is called Get & Transform. Create a Facebook Power Query. If you’re connecting for the first time, you’ll need to sign into your Facebook account. The preview query. In the Editor you will be able to expand columns with a double arrow icon in the right hand corner of the column heading to reveal more data....

In the bell curve, the highest point is the one that has the highest probability of occurring, and the probability of occurrences goes down on either side of the curve. It is often used during employee performance appraisals or during evaluation in exams (ever heard – “You will be graded on the curve?”). Now before I jump in on how to create a bell curve in Excel, let’s get a better understanding of the concept by taking an example....

Let’s take a look. To modify a calculated field, you need to navigate to the Insert Calculated Field dialog box. First, select any cell in the pivot table. Then, on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field. Next, select the calculated field you want to work with from the name drop-down list. You can now update the formula as you like....

Let’s take a look. Before you create a pivot table, make sure your source data is in good shape. Rows and columns need to be consecutive. If you have any blank rows or columns in the data, remove them first. Next, make sure that each column has a unique label in the first row. These labels will appear in the field list once the pivot table has been created. Now select any cell inside the data....

As you can see the duplicate rows are gone after using the Remove Duplicates tool.

Normally, we select the worksheet tab, then spot the cell (which may involve scrolling) and then click on it. In this blog post, learn 2 quick methods to do this. #1 – Using Excel Watch Window to Monitor an Excel Cell Excel Watch Window enables you to keep a track of the values in selected cells. It is a handy feature if you have a couple of cells that you want to monitor as you work with your data....

Let’s take a look. Here we have a worksheet that tracks the number of hours worked and gross pay of a small group of people. Everyone is paid the same hourly wage, so we need to set up a formula that calculates gross pay based on hours worked and a fixed hourly wage. In cell D9, let’s enter the first formula we need: =C9*C6 This works fine. John worked five hours at $11 dollars per hour, so his gross pay is $55....

When to use Excel RIGHT Function RIGHT function can be used to extract text from right of the string. What it Returns It returns the specified number of characters from the right of the string. Syntax =RIGHT(text, [num_chars]) Input Arguments text – The text string that contains the characters you want to extract. [num_chars] – (optional) the number of characters to extract from the right of the text string. If you omit this, is defaults to 1....

Let’s take a look. Here we have the set of test scores we looked at previously. Let’s explore using conditional formatting with icon sets to highlight values in the table. Icon sets are another primary category in the Conditional Formatting menu. Here, you’ll see a large number of icons divided into four main area: Directional, shapes, indicators, and ratings. As with other conditional formats, Excel will build a live preview of each option on the worksheet Let’s choose a simple set of green, yellow, and red circles from the Shapes area....

Let’s take a look. In this worksheet, we have a list of materials to build a dog house in column B, and the cost of each item in column D. We also have the quantity needed for each item in column C. Let’s create a formula that calculates the total cost for each item, taking into account the quantity. This is a perfect application for relative cell references. To start off, we’ll create our first formula in cell E6....

Syntax ISERR(Value) Value (required) – This is the value that is checked for an error. Example In this example we test a few values some of which are errors. Notice the #N/A error results in FALSE while the other errors result in TRUE.

Syntax ISNUMBER(Value) Value (required) – This is the value you want to test if it’s a number. Example In this example we test a few values. Notice the date is still a number even though it is formatted with a dash to separate the year, month and day.

and MAX returns the larger value. Elegant alternative to IF The MAX function can be a compact and elegant replacement for the IF function. For example, the formula above can be written with IF like so: However, the MAX version is 6 characters shorter (11 vs. 17), and contains no redundant references, so it is less prone to errors. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

where “values” is the named range B5:B14. Here, the number 4 specifies MAX, the number 6 is an option to ignore errors, and “values” is the named range B5:B14. With these settings, AGGREGATE returns the maximum in the remaining eight values, 100. Alternative with MAXIFS The MAXIFS function can return the max value in a set of data, after applying one or more criteria to filter out unwanted values. If values in the data set are known to be positive, you can use the following formula to return the maximum value while ignoring errors: This works because the “greater or equal to zero” expression effectively filers out error values, and MAXIFS returns the maximum value from the remaining 8 values, 100....

As I mentioned earlier, every worksheet has an “active cell”, which you can see displayed in the name box. When the active cell is off screen, you can scroll it into view, without actually changing the active cell, using Control + backspace on Windows, Command + Delete on a mac. If you extend this selection in any direction, you’ll see that the active cell doesn’t change. Only the selection changes....

In a workbook that contains multiple worksheets, you can go to the next worksheet using Control + PgDn on Windows, Fn + Control + down arrow on a Mac. To move to the previous worksheet, use Control + Page up on Windows, Fn + Control + up arrow on a Mac. On a Mac, you can also use Option plus right and left arrow keys to move through worksheets. To open a menu that lets you navigate through all worksheets on a workbook, right-click the worksheet navigation area, then select a sheet....

Structured references are used to refer to Excel tables in formulas. The syntax for structured references allows you to precisely target different parts of the table. Let’s walk through some examples. All these formulas refer to Table1 at the left. Since the formulas are outside the table, they all include the table name. The formulas in column I are actually entered as text. In each case, I’ll temporarily convert to a real formula so you can see exactly what range is being targeted....

Where data is an Excel Table in the range B5:F16. The result is $205, the sum of Total where the Color is “Red” OR “Blue”. Note the SUMIFS function is not case-sensitive. SUMIFS + SUMIFS Before looking at the more advanced option shown in the example, it’s important to note that we can solve this problem with two calls to SUMIFS like this: The first SUMIFS sums Total where the color is “Red”, and the second SUMIFS sums Total where the color is “Blue”....

where category is the named range B5:B122. The results in column H are decimal values with the percentage number format applied. The range is the named range category (B5:B122), and the criteria as is supplied as a reference to F5, which simply picks up the text value in column F. As the formula is copied down, it returns the count of each category listed F5:F9. To calculate the percentage shown in column H, we need to divide the count per category by the total count....

This may seem a little silly — how hard can it be to figure out the last day of a month? — but it’s actually a bit tricky, since each month has a different numbers of days, and February changes in leap years. Enter the EOMONTH function EOMONTH takes two arguments: a start date, and months. Months represents the number of months to move in the future or past. So, for example, with May 12, 2017 in cell B5:...