With a laptop keyboard, use Control Shift +. With a full keyboard, use Control + Note: In Mac 2016, this shortcut was changed to Command Shift +, and now Control + I changes text to italic. In previous Mac Excel versions, Control + I inserts a row (when a row is selected) or brings up the Insert Cells dialog when the selection does not include an entire row. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
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.
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....
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....
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....
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....
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....
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....
Which parses a 6-character time string in hhmmss format into a valid Excel time. Note: the examples above use different time format codes as indicated in the screenshot. Context Excel expects times in Excel to be entered with the hour and minute separated by a colon. If you are entering a time with seconds, you’ll need to add another colon to separate minutes and seconds, as seen in the table below: The example on this page shows one way to skip the colons and enter a simple 4-digit or 6-digit text string that represents a time, then parse the text into a proper Excel time with a formula in a helper column....
Fields The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color. The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”: The Date field has been added as a Column field and grouped by year: Helper column alternative As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year....
Fields The source data contains three fields: Name, Age, and Group. Only Name and Age are used in the pivot table as shown: Age is used as a Row field. After Age has been added to the pivot table, it has been grouped as below: Starting and ending value are automatically entered based on the source data. The “by” is set to 10 years, but can be customized as needed. The pivot table maintains age grouping when fields are added or reconfigured....
Fields In the pivot table shown, there are three fields in the source data: Date, Sales, and Filter. Filter is a helper column with a formula flagging the last 12 months. The Date field has been grouped by Year and Month: Formula The formula used in E5, copied down, is: This formula returns TRUE when a date is greater than or equal to the first day of the month 12 months earlier and when the date is less than the last day of the previous month....
How to make this pivot table Author 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.
In the example shown, the formula in C5 is: To get an ordinal suffix for a small set of numbers, you can use the CHOOSE function like this: Here CHOOSE simply picks up a number from column B and uses that number as an index to retrieve the right suffix. A universal formula With a larger range of numbers it’s not practical to keep adding values to CHOOSE. In that case, you can switch to a more complicated formula that uses the MOD function: This formula first uses MOD with AND to “trap” the case of numbers like 11, 12, 13, 111, 112, 113, etc that have a non-standard suffix with is always “th”....
