Posts

Excel lets you customize your workbooks in a variety of ways. One popular way to visually organize your worksheets is to change the color of the sheet tabs. Using different colors for sheets that contain inputs, data, calculations, reports, or visuals can be a helpful way to arrange your workbooks. You might also have a sheet for each month of the year and distinguish each quarter by color. Excel makes it easy to change tab colors, and doing so can help users keep track of important information....

In the example shown, the formula in D5 is: Note: make sure results are formatted as time. As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes of time to the value in A1 like this: With the TIME function You can also add time values with the TIME function. To add 15 minutes to a time in A1, use: The TIME function saves you from having to remember the formula for converting decimal minutes to an Excel time....

But in some cases, you may want to apply conditional formatting to a cell or column based on values in another column. A simple example of this could be when I have the names of students and their scores in two separate columns, and I want to highlight those names where the students have scored less than 35. In this tutorial, I will show you to apply conditional formatting to one column based on the values in another column or cell....

In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data by group, then by score in descending order. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “sortby”. Once we have a match, I’ll press the TAB key to complete. The first argument is called array....

where data is the Excel Table in B5:E16. A similar formula in G12 returns the 3 biggest losers. These results are dynamic and will immediately update if data changes. Read below for a detailed explanation of both formulas. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. If you are new to FILTER, see this video: Basic FILTER function example Top 3 gainers In the example shown, the formula to return the top 3 gainers in cell G5 is: Working from the inside out, the first task is to extract the three rows from the data that have the largest change values....

While the concept of Area Under Curve is quite simple – which is to calculate the total area covered between the curve/line and the axis – there is no direct way to calculate this in Excel. But it isn’t too difficult as well! In this short tutorial, I will show you two ways you can use to calculate Area Under Curve in Excel. So let’s get started! Formula to Calculate Area Under Curve in Excel As I mentioned, there is no direct formula to calculate AUC, but we can calculate it using a helper column and a simple formula....

The results in column E are decimal values with the percentage number format applied. The same formula can be used to calculate things like variance between this year and last year, variance between a budget and actual values, and so on. The concept of variance requires a baseline value and a “new” value. The baseline value is subtracted from the new value and the result is divided by the baseline value....

which ensures the result will never be greater than $1000. Inside MIN, the value in C6 is multiplied by10%, and the result appears at the first number given to MIN. The number 1000 is supplied as the second value. The MIN function simply returns the smaller of the two values: When C610% is < 1000, the result is C610% When C610% is > 1000, the result is 1000 When C610% is = 1000, the result is 1000...

which returns 39, the age of “JILL SMITH”. Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. Working from the inside-out, EXACT is configured to compare the value in E5 against names in the range B5:B14: The EXACT function performs a case-sensitive comparison and returns TRUE or FALSE as a result. Only values that match exactly will return TRUE....

Whenever you select a chart in later versions of Excel, you’ll see three icons appear at the upper right edge: the plus icon, the paint brush icon, and the filter icon. The plus icon is actually a fly out menu called Chart Elements. When you click the icon, you’ll see a list of common chart elements displayed as checkboxes. This menu lets you quickly enable and disable chart elements. Here you’ll find options for a long list of chart elements, including axis and axis titles, the chart title, Data labels, Data table, error bars, gridlines, legend, and trendlines....

Note: you can see the same data in a clustered bar chart. How to make this chart 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.

Date and Time in Excel are stored as numbers. This enables a user to use these dates and time in calculations. For example, you can add a specific number of days or hours to a given date. However, sometimes you may want these dates to behave like text. In such cases, you need to know how to convert the date to text. Below is an example where dates are combined with text....

To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm. Since there are 24 hours in a day, 60 minutes in each hour, and 60 seconds in each minute, you need to divide by 24 * 60 * 60 = 86400 in order to convert decimal seconds to a value that Excel will recognize as time. After dividing by 86400, you can apply a time format of your choice, or use the result in a math operation with other dates or times....

which returns a value of 60, since there are 60 seconds in 1 minute. Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24, convert to decimal minutes by multiplying the value by 1440 (24 * 60) , and convert to seconds by multiplying by 86400 (24 * 60 * 60). With the time value 6:00 cell A1, you can visualize the conversion like this: The Excel time 6:00 converts to 21,600 seconds....

where things is the named range E5:E7. Note: COUNTIF is not case-sensitive. The COUNTIF function counts the number of cells in a range that meet criteria. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers as the result, where each number represents the count of one thing in the criteria range. In this case, the named range things (D5:D7) contains 3 values, so COUNTIF returns 3 results in an array as shown below: Since “apple” appears twice, “pears” appears three times, and “kiwis” appears once, the array contains the numbers 2, 3, and 1....

One of the challenges while creating a dashboard is to present the analysis in limited screen space (preferably a single screen). Hence, it is important to make smart choices while creating the right chart. And here is where Bullet Charts score over others. Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target....

These are helpful when the existing Excel functions are not enough. In such cases, you can create your own custom User Defined Function (UDF) to cater to your specific needs. In this tutorial, I’ll cover everything about creating and using custom functions in VBA. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. What is a Function Procedure in VBA? A Function procedure is a VBA code that performs calculations and returns a value (or an array of values)....

In the example shown, the data validation applied to B5:B11 is: This formula uses the SEARCH function to test user input for each value in the named range “list”. The search logic is “contains” — when a value from “list” is found is found, SEARCH returns the position of the value as a number. If not found, SEARCH returns an error. The ISNUMBER function then converts numbers to TRUE and errors to FALSE, and the double negative operator changes the TRUE FALSE values to 1s and zeros....

Doughnut charts should be avoided when there are many categories, or when categories do not sum to 100%. Pros Simple, compact presentation Can be read “at a glance” Excel will calculate percentages automatically Cons Difficult to compare relative size of slices Become cluttered and dense as categories are added Limited to part-to-whole data Poor at showing change over time Tips Limit categories Consider other charts to show change over time...

Example To use COMBIN, specify the total number of items and “number chosen”, which represents the number of items in each combination. For example, to calculate the number of 3-number combinations, you can use a formula like this: The number argument is 10 since there are ten numbers between 0 and 9, and number_chosen is 3, since there are three numbers chosen in each combination. This result can be seen in cell D8 in the example shown....