Let’s take a look. Here we have the test score table we looked at previously. Currently, there is one conditional formatting rule applied to all test scores in the table. This rule applies a conditional format of a green fill with white text when the test score is greater than 95. Let’s add another conditional format rule to highlight test scores that are lower than 70, in red. As before, start by selecting the cells to format....
Let’s take a look. Pivot table styles are available on the Design tab of the PivotTable Tools ribbon. To apply a pre-built style, select any cell in the pivot table and navigate to the Design tab. All available styles are listed in the PivotTable styles group. As you hover your mouse over each style in the group, Excel will build a preview of that style applied to your pivot table in the background....
Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. 100% stacked charts are focused on proportions, at the expense of actual values. To better explain this, I’ll create a basic stacked area chart, then compare with a 100% stacked version. First, I’ll select data, excluding totals. Then I’ll use recommended charts to insert a stacked area chart. The result shows total sales, and how much each product line contributes to total sales....
Get The Completed Workbook If you work in finance and most other fields, it probably happens a lot that you need to know what calendar quarter a date falls under. Unfortunately there is no Excel function available to return this information based on a given date. Using this formula we can convert any date into a calendar quarter. The MONTH(B3) part of the formula takes the date and returns the numerical month value of the date, so for example MONTH(“2014-07-15”) would return a value of 7....
To create a dynamic named range that refers to this data using the OFFSET function, first identify the first cell of the data in the upper left. In this case, that’s cell B6. To create a named range, we’re going to use the Name Manager. However, it will be easier to enter the formula using the formula bar, so I’ll start by entering the OFFSET function in cell K6, and then use that formula to create a named range in the next step....
A Scroll Bar in Excel is what you need when you have a huge dataset and you don’t want it to hijack your entire screen’s real estate. It’s a great tool to use in an Excel Dashboard where you have to show a lot of data in a limited space. In this step-by-step tutorial, I will show you how to create a scroll bar in excel. You will also learn how to link a dataset to this dynamic scroll bar, such that when a user changes the scroll bar, the data accordingly changes....
What you want to do is just not possible with the native functions. Creating a UDF might be the only solution.The formula you created with native functions are just too long with too many repeated inputs so it’s hard to read. Creating a UDF can simplify this. If you’re not very familiar with VBA, how to open the visual basic editor and where to put the code, then you may want to read this post about How To Use The VBA Code You Find Online....
Let’s take a look. Here is the same pivot table we’ve looked at previously, showing Sales and Orders by product. Let’s add a Value Filter on the Product field that limits products to the top 5 products by Sales. Top and bottom Value Filters are a special kind of Value Filter, so you’ll find the option under Value Filters in the drop-down menu for the field you want to filter....
It’s a numeric value that tells us how two variables are correlated. It tells us how much the dependent variable will change in case there is a change in the independent variable. There are three ways to find the slope of the regression line for a given set of variables in Excel: Using the SLOPE FunctionUsing an Excel Scatter chart In this tutorial, I show you how to calculate slope using each of the above three methods....
Example 1 - space instead of colon In the screen below, the formula in C9 returns the #NULL error: In this case the input was meant to be the range C3:C7, but the colon did not get typed. Once the colon is added, the error is fixed: Example 2 - space instead of comma In the example below, the formula in C5 returns the #NULL error: Here, a space was typed instead of a comma between F2 and I2....
For example, you may have a list of dates where you want to highlight all the weekend dates. This can easily be done using Conditional Formatting with a little bit of formula know-how. In this tutorial, I will show you how to highlight the weekend dates in a date data set. The method covered here can also be used to highlight any kind of dates (say Mondays or Tuesdays or alternate days) So let’s get to it!...
By default, Excel does this by inserting page breaks at regular intervals in the worksheet while printing. This is done based on the paper size, margins, and scale options. But in case you need more control and want to decide how much content should be printed on Page 1 and where it should break and go to the next page, then you can manually insert a page break in between the pages....
Excel formulas are easy to create and edit in Excel. You can easily edit a formula through the formula bar or directly in the cell. While this makes it convenient to create formulas in Excel, it comes with a few disadvantages as well. Consider this. You are going through a worksheet full of formulas, and you accidentally hit the delete key, or backspace key, or some other number/alphabet key. Now you’ll be lucky if you’re able to spot the error and correct it....
Among all these Excel chart types, there has been one that has been a subject of a lot of debate over time. …the PIE chart (no points for guessing). Pie charts may not have got as much love as it’s peers, but it definitely has a place. And if I go by what I see in management meetings or in newspapers/magazines, it’s probably way ahead of its peers. In this tutorial, I will show you how to create a Pie chart in Excel....
For example, if you have the name and designation data of people, you may want to remove the designation after the comma and only keep the name (or vice-versa where you keep the designation and remove the name). Sometimes, it can be done with a simple formula or a quick Find and Replace, and sometimes, it needs more complex formulas or workarounds. In this tutorial, I will show you how to remove the text before or after a specific character in Excel (using different examples)....
However, there is no inbuilt functionality in Excel to sort the cells based on the length of the text in the cells. In one of my Excel training sessions, I was asked whether it is possible to ‘sort by the length of text in the cells in Excel or not. I could come up with two simple methods to do this (there could be more I am sure). In this tutorial, I’m going to share these two simple methods you can use to quickly sort by the length in Excel....
This happens because, somehow, the Scroll Lock has been enabled, and you need to turn off the scroll lock for things to work as expected in Excel. In this short tutorial, I will show you a couple of methods to quickly turn off the Scroll Lock in Excel. How to Know that Scroll Lock is Enabled? Before I tell you how to turn off the scroll lock in Excel, I must tell you how to identify whether it is enabled or disabled....
When to use Excel TEXT Function Excel TEXT function can be used when you want to convert a number to text format and display it in a specified format. What it Returns It returns text in the specified format. Syntax =TEXT(value, format_text) Input Arguments value – the number that you want to convert into text. format_text – the format in which you want to display the number. Additional Notes This is a useful function when there is a need to show numbers in a format, or a need to combine numbers with text/symbols....
Let’s take a look. In column B of our table we have a set of numbers in General format. Let’s first copy the numbers to the rest of our table. Now let’s apply the Percentage format. Although percentage is listed in the Number format menu on the ribbon, it’s faster to use the Percentage button just below the menu. As we can see, Excel displays the numbers in standard percentage format....
For example, below is a list where I have company names which are hyperlinked to the company website’s URL. When you click on the cell, it will automatically open your default browser (Chrome in my case) and go to that URL. There are many things you can do with hyperlinks in Excel (such as a link to an external website, link to another sheet/workbook, link to a folder, link to an email, etc....
