Sooner or later you’ll come across the need in Excel too! In this post, I’ll show you 3 easy ways to do this in Excel. Video Tutorial What Does Transposing Data Mean? To transpose literally means to cause two or more things to change places with each other. When dealing with data, this means you want to change rows into columns and change columns into rows like in the above example....
Tables have a lot of great features and are perfect for working with and storing your tabular data. But one feature that many users aren’t keen on is the default table formatting that’s automatically applied when you convert your range to a table. After creating a table, you might want to get rid of this default format and opt for a less distracting look. This post will show you how to remove the table formatting in your Excel table....
Metadata is just data about the file such as when the report was created by the system or the time period of the reporting. In the case of CSV files, this will likely be in the first couple of rows of data before the report data begins. When importing the data with power query, you would remove the top rows that contain the metadata and then promote the first row of the report data to column headers....
This is a huge milestone for me and I am completely overwhelmed by the love and support I have got from my readers. I started this journey ~2 years ago and it has been a heck of a ride so far. I started this blog an effort to share what I learn every day about Excel. And to know that more than 2,000 people benefit from what I do is a nice and warm feeling....
If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (""), which looks like a blank cell. Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated like this: In other words, if C5 < 100, output “low” 1 time. In cell C6, the formula is evaluated like this: In other words, if C6 < 100 is FALSE, output “low” zero times....
Which can be copied across row 8 to pickup every 3rd value from row 5. The OFFSET function is designed to create references by using “offsets” from a starting cell. In the example shown, the starting cell is C5, provided to OFFSET as an absolute reference so it won’t change as the formula is copied: For the rows argument, we provide zero, since we want to stay in the same row....
In the example shown, the formula in F8 is: The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5: This returns an array of TRUE/FALSE values like this: {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE} The second expression tests every holiday date to see if it’s less than or equal to the end date in F6: which returns an array of TRUE/FALSE values like this: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE} The multiplication of these two arrays automatically coerces the TRUE/FALSE values to ones and zeros, resulting in arrays that look like this: After multiplication, we have just one array like this: Finally, SUMPRODUCT sums the items in the array and returns 4....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Discount rate is based on the concept of the time value of money. The discount rate is the interest used to convert between future value and present value. A future value can be “discounted” by a given interest rate to determine the present value. Example In the example shown, we want to find the discount rate for a bond with a price of $89.50 and a redemption value of $100....
In fall 2018, Microsoft announced a new feature in Excel called “dynamic array formulas”. In a nutshell, this feature allows formulas to return multiple results to a range of cells on the worksheet based on a single formula entered in one cell. This behavior is called “spilling” and results appear in a “spill range”. All results are dynamic – if source content changes, the results dynamically update to stay in sync....
FACT takes just one argument, number, which should be a positive integer. If number is not an integer, the decimal portion of number will be removed before the factorial is calculated. Examples If number is not an integer it will be truncated to an integer: When number is zero, FACT returns 1: If number is negative, FACT returns the #NUM! error: Notes Number cannot be negative or FACT will return the #NUM error....
When to use Excel INDEX Function Excel INDEX function can be used when you want to fetch the value from a tabular data and you have the row number and column number of the data point. For example, in the example below, you can use the INDEX function to get the marks of ‘Tom’ in Physics when you know the row number and the column number in the data set....
Note: the NPER function returns the number of periods for a series of cash flows (like a loan repayment schedule) whereas PDURATION returns the number of periods for a single sum to achieve a certain amount. Example Assume you have $5,000 to invest at an annual rate of 5%. When interest is compounded monthly, how long will it take for the initial investment of $5,000 to reach $10,000? In the example shown, the formula in F5 is: Notice because rate must be provided as interest rate per period, it is given here as the annual interest rate divided by the number of compounding periods per year (12)....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Flash fill is a new feature added in Excel 2013. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Note: In Excel 365, the original “comment” was renamed to “note” and a new “threaded comment” was added to allow back and forth comments by reviewers. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
