While most of the in-built features and default settings are meant to be useful and save time for the users, sometimes, you may want to change things a little. Converting your numbers into text is one such scenario. In this tutorial, I will show you some easy ways to quickly convert numbers to text in Excel. Why Convert Numbers to Text in Excel? When working with numbers in Excel, it’s best to keep these as numbers only....
Here we have annual sales figures for a small company. I’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. Charting works best if the data is structured in a simple grid without blank rows or columns, so, I’ll remove this blank row. To create a chart, start by selecting the data. In most cases, you’ll want to exclude totals, but go ahead and include labels, so that Excel can use these in the chart....
Let’s take a look. Here we have a list of materials to build a dog house which includes quantity, unit cost, and a total. Let’s add a formula that calculates sales tax. One way to do this is to name a range on the worksheet, and use that name in the formula. For example, I can name cell I4 “tax_rate,” and then add formulas in column F that multiply the total in column E by the tax rate using that name....
It is possible to create a multi-category chart in Excel (as shown below). The trick is to arrange the data in a certain way, that makes Excel interpret as having multiple categories. Suppose I have the data for the monthly expenses I make on the food items (as listed below). The idea to create a chart for these items, and also categorize these as Fruits, Vegetables, and Packaged Food....
But at the same time, there are some simple things that are missing and often leads to a lot of frustration. For example, there are so many ways for you to quickly select cells and ranges but there was no way for you to deselect a range of cells after you have selected it. Imagine you have to highlight a range of cells and you accidentally end up selecting an extra row or column....
I remember spending a lot of time calculating factorials as a part of the probability calculations. But if you’re using Excel and somehow get into a situation where you need to calculate factorial, you don’t need to take out your pen and paper – a simple Excel formula would do that for you. What is a Factorial? If you already know this and are here just to know how to calculate it in Excel, jump to the next section....
Let’s take a look. To filter data in Excel, first select a cell anywhere in the list or table you’d like to filter; then, click the Filter button on the Data tab of the ribbon. You’ll know that filtering is enabled when you see the small arrows appear to the right of each column heading. Each arrow provides a drop down menu that contains tools to filter on that column....
In our example MAX(Range) returns the value 9 since 9 is the maximum value in the range of values {1;0;8;6;9;7;9;6}. MATCH(9,Range,0) looks for a 9 in the Range and will return the position of the first 9 it finds. The 0 in the formula is a predefined Excel parameter that tells the MATCH function to find an exact match in the Range. In our example MATCH(9,{1;0;8;6;9;7;9;6},0) returns the value 5 since the first 9 in {1;0;8;6;9;7;9;6} is in the 5th position....
A common scenario is where you want to plot X and Y values in a chart in Excel and show how the two values are related. This can be done by using a Scatter chart in Excel. For example, if you have the Height (X value) and Weight (Y Value) data for 20 students, you can plot this in a scatter chart and it will show you how the data is related....
When it creates the code, elements of it will be hardcoded. For example if you’re importing data from an external source the folder path and file name will both be a hardcoded (static or unchanging) string of text in the M code. If you want to update the folder path or file name to be imported then you need to go into the advanced editor and update the path and file name....
Excel Crashed! Imagine working on that urgent report or that extremely important dashboard where you have done such a fantastic job. You are proud of yourself and all the hard work you have put into it. But you forgot a key step. You didn’t save the Excel file (gasp!!!) Ok, let me cut down on the dramatics and come straight to the point. Excel has a tendency to crash, especially when you need it not to....
Looking up a value based on one criteria Let’s say we have a table with employees and their total sales and we want a formula to return the total sales based on the employee. Now, if we want to get Allison’s sales, we could use a VLOOKUP formula using this formula. Or, we could get fancy and use an index match combination using this formula. Looking up a value with a row criteria and a column criteria Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West....
When you create a pivot table, you have your choice of three layouts. In this video, we’ll look at each layout. Once you have a pivot table, you can change layouts using the Report layout menu, on the Design tab of the pivot table tools ribbon. By default, each new pivot table you create will use the Compact layout. But you can easily switch to Outline layout, or to Tabular layout....
One solution is a formula that uses the IF function together with the SEARCH and ISNUMBER functions. In the example shown, we have a list of email addresses, and we want to extract those that contain “abc”. In C5, the formula were using is this: If “abc” is found anywhere in cell B5, IF will return that value. If not, IF will return an empty string (""). In this formula, the logical test is this bit: This snippet will return TRUE if the the value in B5 contains “abc” and false if not....
which returns a value from the first non-blank cell, B5, C5, D5, or E5, respectively. When all cells are blank, the formula returns “no value”. The value returned when all cells are blank can be adjusted as desired. This expression is used four times in the formula shown in the example, in order to test four different cells in a particular order. The overall structure of this formula is what is called a “nested IF formula”....
In this worksheet, we have a list of the Wimbledon Men’s Singles Champions since 1968, in a table called “data”. How can we list names that appear more than once, along with a count? To start off, let’s look at how we might count the names in the data with the COUNTIF function in column H. For range, we want all the names in the source data. Now, for criteria, we want exactly the same list....
Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter They key is in understanding how FREQUENCY gathers numbers into “bins”. Each bin represents an upper limit, and generates a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number. The gist of this formula is that it creates a new bin at the end of each winning streak using the id of the subsequent loss....
where data is the named range B5:B15. Note: this formula performs a case-sensitive comparison. See notes below for other options. Note: this formula performs an exact match when both the lookup value and array values are greater than 255 characters. See below for other options. The string we are testing with in cell E5 is 373 characters as follows: Lorem ipsum dolor amet put a bird on it listicle trust fund, unicorn vaporware bicycle rights you probably haven’t heard of them mustache....
where “level” is the named range C5:C9, and “points” is the named range B5:B9. Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2. The small twist is that we add 1 to this result to override the matched result and return 3 as the row number for INDEX....
Note: this is an array formula and must be entered with control + shift + enter. Here, the formula uses the COLUMN function to get a set of “relative” column numbers for the range, explained in detail here. The result is an array like this: This array goes into the MOD function as the number argument: where L5 is the the value to use for “nth”. The MOD function returns the remainder for each column number divided by N....
