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.
Excel provides four settings for Tables you should be aware of. The first option controls whether a table range will automatically adjust as data changes. By default, this option is enabled, since this feature is a key benefit of tables. For example, if I enter a new row in this table, the table expands to include it. The same is true when I add a new column header. In Windows, the setting that controls this behavior is in the proofing area, under Autocorrect settings....
Note while VAR.S ignores text and logicals passed into as cell references, it will evaluate logical values, and text representations of numbers hardcoded directly as arguments. In other words, VAR.S will ignore FALSE when it appears in a range like A1:A10, but will evaluate FALSE when it is provided as an argument. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VAR.S assumes arguments a sample of data, not entire population....
The XMATCH function takes four arguments: lookup_value, lookup_array, match_mode, and search_mode. Lookup_value is the value to look for, and lookup_array is the range or array to search. Both arguments are required. The match_mode argument controls what kind of match is performed (exact, next smallest, next largest, or wildcard), see match_mode below for details. Finally, search_mode controls the search direction - whether XMATCH should start at the beginning of the array, at the end of the array, or if XMATCH should perform a binary search....
There is no inbuilt function in Excel to extract the numbers from a string in a cell (or vice versa – remove the numeric part and extract the text part from an alphanumeric string). However, this can be done using a cocktail of Excel functions or some simple VBA code. Let me first show you what I am talking about. Suppose you have a data set as shown below and you want to extract the numbers from the string (as shown below):...
This simple example shows how present value and future value are related. In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this: The formula to calculate future value in C9 is based on the FV function: The formula to calculate present value in F9 is based on the PV function: No matter how years, compounding periods, or rate are changed, C5 will equal F9 and C9 will equal F5....
B5 contains a date value for January 5, 2016. The DAY function returns the number 5 representing the day component of the date. Note that you can use DAY to extract the day from a day entered as text: But this can produce unpredictable results on computers using different regional date settings. In general it’s better (and more flexible) to supply an address to a cell that already contains a valid date value as the argument for DAY....
A trendline, as the name suggests, is a line that shows the trend. You’ll find it in many charts where the overall intent is to see the trend that emerges from the existing data. For example, suppose you have a dataset of weekly visitors in a theme park as shown below: If you plot this data in a line chart or column chart, you will see the data points to be fluctuating – as in some weeks the visitor numbers decreased as well....
In this worksheet we have a chart with basic formatting. If you want to apply a certain font, font color, or font size globally to a chart, the fastest way is to use either the home tab on the ribbon, or the Font command in the right-click menu. In either case, start by selecting the chart area. On the Home tab of the ribbon, you can use the font controls on the font area....
Let’s take a look. Here we have a pivot table that shows product sales by category and product, grouped by year and quarter. Whenever you have a pivot table that summarizes data into various groups, you can interactively expand and collapse those groups. Pivot tables have special expand and collapse buttons that can be enabled on the Options tab of the PivotTable Tools Ribbon. When enabled, you can use these buttons to expand and collapse various groups in the table....
Excel allows you to format dates in many different ways. You can choose to show the date in a short date format or in a long date format. You can also only show the day number, the month name, or the year from a given date. In this short Excel tutorial, I will show you some easy methods to get the month name from a date in Excel. So, let’s get started!...
Example Generic Formula What It Does This formula will return the percent of the month completed for a given date. How It Works DAY(Date) will return the day part of the given Date. In our example DAY(“2017-09-10”) returns a value of 10. EOMONTH(Date,N) will return the last date in the month which is N number of months before or after the Date. If N is 0 then this will be the last date in the same month as the given Date....
Making VLOOKUP Case Sensitive Suppose you have the data as shown below: As you can see, there are three cells with the same name (A2, A4, and A5) but with a different letter case. On the right (in E2:F4), we have the three names (Matt, MATT, and matt) along with their scores in Math. Excel VLOOKUP function is not equipped to handle case-sensitive lookup values. In this above example, no matter what lookup value case is (Matt, MATT, or matt), it’ll always return 38 (which is the first matching value)....
Here we have data from a survey we ran recently. We’ve got around 3900 responses to the question “What version of Excel do you use most?". Let’s plot the data in a chart. The first step is to build a summary table. First, I’ll convert the data to an Excel table. This step is not required, but it will make the formulas easier to write. For the first formula, I need to count all responses....
By default, this setting is disabled, which means that when you print a worksheet, the comments are not printed. While Excel provides a couple of options to print comments, it is not readily available in the ribbon. Print Comments in Excel using the Page Setup from Ribbon If you have a lot of comments in a worksheet and you print it, by default the comments would not get printed. You need to make a few changes in the settings to get this done....
Excel gives you a lot of options when you’re trying to print your work. You can choose to print the entire worksheet, a specific area in the worksheet, print multiple sheets, or all sheets at one go. In this tutorial, I will show you how you can print multiple sheets in Excel at one go. These could be some selected sheets or all the sheets in the workbook. And in case you want to print a specific area in multiple/all sheets, you can do that too with a little bit of VBA magic....
In today’s ExcelJet tip, we’ll show you a cool way to delete blank rows, even hundreds or thousands of blank rows, in record time. Even better, Excel does all the hard work for you. Let’s take a look. Here we have a really big list that contains a lot of empty rows. If we hop down to the bottom of the sheet, then back up to the bottom row, we can see that we have over 36,000 rows, and several thousand of these rows are empty....
In this detailed guide, I will cover all that you need to know to get started with recording and using macros in Excel. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. What is a Macro? If you’re a newbie to VBA, let me first tell you what a macro is – after all, I will keep using this term in the entire tutorial....
One of the best features of tables is called “calculated columns”. Calculated columns help you enter and maintain formulas in Excel tables. To explain how this works, let me first add a formula to this data, which is not an Excel Table. A quick way to copy down the formula is to double-click the fill handle. Since every cell has a price, Excel copies the formula to the bottom. Next, I’ll add a formula to calculate a 7% tax....
where data is an Excel Table in the range B5:C15. AND and OR limitations In this example, we want to test each row in the table with the following logic: Color is “Red” OR “Blue” AND Qty > 10. For each row in the table, we want a TRUE or FALSE result. If we try to use a formula like this: The formula will fail because the AND function and the OR function both aggregate values to a single result....
