Can you easily unpivot the data? Yes! I have created a pivot table based on some source sales data. Rows area – I’ve added Customer Name, Customer Country and Sales Channel.Column area – I’ve added the Product sold.Values area – I’ve added the Total. We can see this creates a new set of data where each product has its own column heading. This is pivoted data. This the original source data....

Syntax DAYS(Start Date, End Date) Start Date (required) – This is the starting date of the period.End Date (required) – This is the ending date of the period. Example In this example we find the number of days between two dates. Notice that if the end date is older than the start date the result is a negative number. The same result can be obtained by subtracting the start date from the end date....

Example Generic Formula What It Does This formula will calculate the volume of a sphere given its radius. How It Works The volume of a sphere is given by the above formula where π is a constant approximately equal to 3.14159265 and r is the radius. Excel has this constant built in as a function with no parameter inputs PI(). The POWER function will take any number and raise it to the power of any other number....

People have been doing this since early versions of Excel and creating relationships between different tables using VLOOKUP or INDEX and MATCH. The good news is that more and more database-like functionality is being added with each version, and since Excel 2013 we’ve been able to add relationships to tables. This example has an Orders table that contains data related to customer orders such as the Customer ID, Product Sold, and the Total....

The Quick Access Toolbar is a set of icons that allows you to easily access the commands you use the most. By default the quick access toolbar only contains 4 commands but the commands it contains can be customized to suit the user. Changing The Quick Access Toolbar Location If you don’t like the default location above the ribbon then you can move the Quick Access Toolbar below the ribbon....

Here are 12 great keyboard shortcuts to quickly format your data.

An issue that comes up quite often in Excel is numbers that have been entered or formatted as text values. This can cause many headaches when trying to troubleshoot why a formula like a SUM is not giving the correct answer. Even though the data looks like a number, it can actually be a text value and will be ignored from any numerical calculations like a sum. In this post, I will show you how you can identify such numbers which are stored as text values, and 7 ways you can use to convert the text into a proper number value....

There’s an Index Column button in the Add Column tab of the power query editor. This will add a column containing a sequentially increasing integer. There are also options for which number to start the sequence with and how much to increment each row. If we add an index column starting at 1 and increment by 1 for each row, then this can be used as a serial number in our data....

The Formula Bar is where data or formulas you enter into a worksheet appear for the active cell. The Formula Bar can also be used to edit data or formula in the active cell. The active cell displays the results of its formula while we see the formula itself in the Formula Bar. Expanding Or Contracting The Formula Bar The Formula Bar can be expanded either vertically or horizontally depending on your needs....

Get The Completed Workbook 1 Formatting a 7 digit number Let’s say we have a list of phone numbers and we want to have them formatted in the nice usual readable way. For example instead of seeing 2225678 we want to see 222-5678. We can use the following formula. 2 Formatting a 7 digit number with an area code What if the number has an area code and we want to format the phone number like this, (333) 555-6666?...

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....

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

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.

An Excel project may require you to summarize your data by date intervals. One such date interval is the quarter. A quarter is a three-month interval, so there are four quarters in every year. These are the standard calendar year quarters. Quarter 1 includes all dates between January 1st and March 31st.Quarter 2 includes all dates between April 1st and June 30th.Quarter 3 includes all dates between July 1st and September 30th....

MOD(Range,2)=1 will create an array of Boolean values where TRUE will mean the number is odd and FALSE will mean the number is not odd. 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 odd numbers in the range. In our example MOD({1.36;-2;2;9;10;7;1.33;8},2)=1 will result in the following Boolean array....

It’s a great data structure for computers to read, but it’s a bit less accessible for a human to read. In this post we’ll take a look at how to import an XML file into Excel and turn it into a more friendly table format. Excel has support for XML files and it’s actually really easy to import and covert them to tables using power query. You can download the example files used in this post here....

Syntax RIGHT(Text, Number) Text (required) – This is the text string you want to return the right most characters from.Number (optional) – This is the number of characters you want returned. If no value is entered, only the last character will be returned. Example In this example we get the right few characters of a text string. Notice that negative numbers result in a #VALUE! error and non whole numbers are rounded down to the whole integer....

Syntax UPPER(Text) Text (required) – This is the text string you want to turn into all uppercase letters. Example In this example we convert some text into uppercase.

If your text data is long, you can increase the cell width to fit the data length. A better option might be to wrap the text to increase the row height so the data fits in the cell instead! In this post you’ll learn 3 ways to wrap your text data to fit it inside the cell. What is Text Wrap? This means that if text is too long to fit inside its cell, it will automatically adjust to appear on multiple lines within the cell....