Posts

Web pages, FacebookExcel, CSV, XML, Text or Hadoop (HDFS) FilesA FolderVarious databases like MS Access, SQL Server, MySQL, Microsoft Azure SQL, Oracle, IBM DB2, PostgreSQL, Sybase, Teradata, OData etc… This is available as an add-in for excel 2010 professional plus or 2013 and comes already built in for Excel 2016. You can download Excel Power Query here from Microsoft. Unfortunately, if you’re not running Excel 2010 professional plus or 2013, then you will need to upgrade to Excel 2016 in order to use this feature as it’s not available for previous versions of Excel....

Let’s take a look. Here we have a blank pivot table. Let’s add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the type of calculation being used on the field. In this case, we get Sum of Total Sales. If we change the calculation to Count, the field is automatically renamed to Count of Total Sales....

Sometimes, you may need to send a part of the worksheet or a single excel worksheet from the workbook to your colleague or boss. While there can be other ways of doing this, in this tutorial, I’ll show you the fastest and the easiest way to email a single Excel worksheet or a part of the worksheet. But there is some pre-work. You need to add an icon to the Excel Quick Access Toolbar....

When to use Excel REPT Function REPT function can be used when you want to repeat a specified text for a certain number of times. If you’re wondering what can be the use of just repeating characters, check out the examples covered later in this tutorial. What it Returns It returns a text string that has the specified text repeat for the specified number of times. Syntax =REPT(text, number_times)...

Let’s take a look. Here we have a pivot table showing sales by Region. Let’s add a column chart. Notice our chart is using the general number format. That’s because we haven’t yet set a format in the pivot table. Let’s do that now. Notice that both the pivot table and the pivot chart pick up the currency format. Now since they take up a lot of space, let’s remove field buttons that appear in the chart....

Let’s take a look. Here we have a set of random times in column B. First, I’ll add a formula to column C to pick up the time values in B and format them in General format, so you can see the raw time value. Now let’s extract the components of each time using Excel’s HOUR, MINUTE, and SECOND functions. I’ll extract the hour value using a function called HOUR....

where “data” is the named range B5:D10 To get only the first row number, we use the MIN function like this: which returns the lowest number in the array, 5. Once we have the first row, we can just add the total rows in the range then subtract 1 to get a final result. We get total rows in the range with the ROWS function, and a final result is determined like this:...

which returns the lowest Monday tide in the data, -0.64 To retrieve the date of the lowest Monday tide, the formula in I7 is: Where the worksheet contains the following named ranges: date (B5:B124), day (C5:C124), time (D5:D124), pred (E5:E124), tide (F5:F124). Both are array formulas and must be entered with control + shift + enter. Data from tidesandcurrents.noaa.gov for Santa Cruz, California. working from the inside out, the first IF checks if the day is “Mon”, based on the value in I5: If the result is TRUE, we run another IF: In other words, if the day is “Mon”, we check if the tide is “L”....

The formula returns the formatted strings as seen in column D. To format a text string with 12 characters in the same way, you can use a formula like this: Working from the inside out, the SEQUENCE function is used to generate an array of 6 numbers used as the start_num argument in the MID function: These are returned directly to the MID function: With the text “112233445566” in B5, the MID function returns an array of 6 strings: This array is returned to the TEXTJOIN function as the text1 argument, and with the colon (:) as the delimiter from C5, we have: The TEXTJOIN function concatenates the 6 strings together using a colon, and returns a single string as a final result: 11:22:33:44:55:66 The formula in D6 works exactly the same, except it uses the hyphen in C6 to join the strings: 11-22-33-44-55-66...

This template allows you to pull data from Moz Open Site Explorer (OSE) into Excel using Power Query (Get & Transform in Excel 2016). Moz OSE is an online tool that allows you to see what sites link to a given site (known as a backlink). Links to a site is one of the top ranking factors for search engines like Google. More backlinks from higher quality sites means higher search ranking!...

Note: this is an array formula and must be entered with control + shift + enter, unless you are using Excel 365, where array formulas are native. Because we are comparing one cell value to values in four other cells, the result is an array with four TRUE or FALSE values. In row 5, all values are equal, so all values are TRUE: This array is returned directly to the AND function, which returns TRUE, since all values in the array are TRUE....

where location (B5:B12) and distance (C5:C12) are named ranges. Working from the inside out, we are using the XMATCH function to find the position of the nearest location: We do that by setting lookup value to zero (0), lookup array to the distance (C5:C12), and match mode to 1. A match mode value of 1 tells XMATCH to find an exact match or next largest value. Since lookup value is provided as zero (0), XMATCH will find the first distance greater than zero....

which trims " miles" from each value returning just the number. The VALUE function converts text to a numeric value. The challenge, for values with variable length, is that we don’t know exactly how many characters to extract. That’s where the LEN function is used. Working from the inside out, LEN calculates the total length of each value. For D6 (736 miles) the total length is 9. To get the number of characters to extract, we subtract 6, which the length of " miles", including the space character....

which generates a series of 12 dates, incremented by one year, beginning with May 1, 2019. SEQUENCE can generate results in rows, columns, or both. In this case, SEQUENCE is configured to output an array of numbers that is 12 rows by 1 column: The starting number is the year value from B5, and the step value defaults to 1, so SEQUENCE outputs an array like this: This array is returned to as the year argument inside the DATE function, which causes results to spill into the range D5:D16....

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt. To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text. This formula uses this concept to figure out where the unit of measure begins....

The result is the square root of each number in column B. To get the square root of 16: To get the square root of a number in cell A1: Negative numbers If you give SQRT a negative number, it returns a #NUM! error: To use the SQRT function with negative numbers you nest the ABS function inside SQRT like this: The ABS function converts the negative number to a positive number and returns the result to the SQRT function, which calculates a final result....

If you are creating an Excel spreadsheet for other users to input data, then dropdown lists are very useful to control what data they are entering. This way you can ensure that they will not enter incorrect data which will produce errors in your spreadsheet when calculations are made based on the user input. Dropdown lists should be familiar as you will frequently find them on the web or while working in other applications....

If you’re wondering which one scores higher in the VLOOKUP vs INDEX/MATCH battle, this article will try and answer it. During one of my Excel Training sessions, I was surprised to find out that most of the people were aware and, in some cases, proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combo. And when I mentioned it, they had a lot of queries:...

Within a few hours, it was shot down by other excel experts as it contained volatile formulas. I immediately recognized the cardinal sin I had committed. So with this confession, let me share what I have learned about volatile functions in Excel. In plain simple terms, it is a function that will make your excel spreadsheet slow, as it recalculates the formula again and again. A number of actions can trigger this (described later in this post)....

which calculates the volume of a rectangular prism, given the length in column B (l), the width in column C (w) and the height in column D (h). Units are indicated generically with “u”, and the resulting volume is in units cubed (u3). This formula can be represented in Excel with the multiplication operator (*) like this In the example shown, the formula in E5 is: As the formula is copied down the column, it calculates a new volume in each row, using the length in column B (l), the width in column C (w) and the height in column D (h)....