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.
On a Mac, the control key (⌃) can be used instead of the command key (⌘). 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.
On Windows, F4 also works. 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.
On Windows, you can also use Control + Y for this shortcut. You can add the “Repeat” command to the Quick Access Toolbar to see when an action can be repeated — when it’s possible to repeat an action, the command will highlight and become available. On a Mac, the number of actions that can be repeated is more limited. 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.
When array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on. TRANSPOSE can be used with both ranges and arrays. Transposed ranges are dynamic. If data in the source range changes, TRANSPOSE will immediately update data in the target range....
XLOOKUP can find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (see match type and search mode details below). Compared to older functions like VLOOKUP, HLOOKUP, and LOOKUP, XLOOKUP offers several key advantages. Not found message When XLOOKUP can’t find a match, it returns the #N/A error, like other match functions in Excel....
where data (C4:L6) and group (C5:L5) are named ranges. The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation. The formula in B5 is: Working from the inside out, the include argument for FILTER is a logical expression: When the logical expression is evaluated, it returns an array of 10 TRUE and FALSE values: Note: the commas (,) in this array indicate columns....
In case you prefer reading over watching a video, below is the complete written tutorial. Sometimes in Excel, you may want to hide zero values in your dataset and show these cells as blanks. Suppose you have a dataset as shown below and you want to hide the value 0 in all these cells (or want to replace it with something such as a dash or the text ‘Not Available’)....
And I got one last week too. So I decided to create a tutorial and a video on it. It will save me some time and help the readers too. Below is a video where I show how to highlight the active row and column in Excel. In case you prefer written instructions, below is a tutorial with exact steps on how to do it. Let me first show you what we are trying to achieve....
In Excel 2016+, a histogram is built-in chart type. Pros Simple to create in Excel 2016+ At-a-glance visualization of the distribution of numerical data Cons Less common chart type not as familiar to many people Built-in histogram chart type has limited options 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....
One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest information. To illustrate, I’ll add some formulas to this worksheet to report on the size of this table. The first formula counts rows with the ROWS function. The second formula counts columns with the COLUMNS function....
Let’s take a look. Here we have a pivot table that shows sales by Product. If we wanted to filter the entire pivot table by Region, we know from an earlier video that we could just add Region as a Report Filter, and use it to filter as needed. Slicers work the same way as Report Filters, but they are easier to use. To show how slicers work, let’s add a slicer for Region and try it out....
Enter the comment format editing mode by clicking on the comment and you will see small squares around the comment. In addition to changing the shape, you can also change a variety of other style options. Wow, so fancy!
Personally, I think the Excel ribbon was a huge improvement over the earlier menu option, as it made it more user-friendly for people to find useful options in one place. While it wasn’t possible to customize the Excel ribbon in the initial versions, from Excel 2010 onwards, Excel allowed users to make their own customizations to the ribbon. With customizations in Excel ribbon, you can create your own Tabs that can include your most used commands, you can reorder the default tab/group placements, and you can also hide some of the tabs in case you don’t use them in your work....
When plotting unsummarized data, the chart filter may not be convenient. For example, here we have historical stock data for Microsoft covering more than 15 years in more than 100 rows of data. If I insert a line chart, Excel automatically builds a condensed horizontal axis and plots all the data. And I can easily use the Chart Filters menu to exclude everything but the close price. But what if I only want to show data from 2016 and 2017?...
She did some scoring in Excel and created Harvey balls using conditional formatting. She wanted to display the Harvey Balls as well as the score together. She also wanted to increase the font size of the Harvey Balls only and not the font size of the score value. Below is the image she sent me along with the query: The first thought that came to my mind was to selectively increase the size of the Harvey Balls only....
No matter how many rows you add or delete, the number of rows in the worksheet never changes. When you insert rows, rows are pushed off the worksheet at the bottom. When you delete rows, new rows are added to the bottom. Let’s take a look. To insert a row in Excel, first select the row below where you want the new row to be. Then, click the Insert button on the ribbon....
In this tutorial, I’m going to show you how to create a calendar in Excel that automatically updates when you change the month or the year value. I will show you the exact process to create the interactive monthly and yearly calendar, and I also have these as downloadable Excel files, so that you can use them offline. You can print these calendar templates and manually create the schedule on paper....
As a part of it, I had to make sure my reports were easy to read and also take less screen space (apparently, clients hate scrolling back and forth). And the functionality of rotating text came in useful in such reports. It’s especially useful when you have a bigger heading and a small number (as shown below). Here, headers in Row 1 take less space as these are at an angle (as compared to when they are horizontal)....
