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.
The Solution Exceljet’s Excel Shortcut Book brings together Excel’s most important shortcuts in one easy to use PDF. All shortcuts are organized into logically grouped sections, and color-coded for easy reference for both PC and Mac. With a professional page layout and crisp PDF rendering both on screen and on paper, this is one shortcut reference you don’t want to be without. With 14 pages of shortcuts, this book is BIG....
The STOCKHISTORY function accepts five primary arguments, and six additional property arguments to retrieve additional information. Stock and start_date are the only required arguments. Each argument is described in detail below. Additional properties are described in the table here. Note: The safest way to provide the start_date and end_date, is as a reference to a cell that contains a valid date, or with the DATE function. This is because dates input as text values can sometimes be misinterpreted....
When it’s way past your work time, you think about that to-do list (stare at it if you have it written) and curse the world for not having enough time in the day. Sounds familiar? If you are nodding your heading in agreement, you – my friend, are suffering from an acute condition of expanding-to-do-list. Well, I am neither a brain doctor nor a self-help guru. I can not help you in overcoming procrastination and getting your work done....
TRIMMEAN works by first excluding values from the top and bottom of a data set, then calculating mean. The number of data points is provided as a percentage. The percentage can be input either in decimal format or percent format: It’s important to note that TRIMMEAN rounds excluded data points down to the nearest multiple of 2. For example, with 50 data points, 10% equals 5 values. In this case, TRIMMEAN will round 5 down to 4, then exclude two values from the top, of the data set, and two values from the bottom of the data set....
In the generic formula above, “N” represents the “nth line”. Note: In older versions of Excel on a Mac, use CHAR(13) instead of CHAR(10). The CHAR function returns a character based on it’s numeric code. The number of spaces used to replace the line delimiter is based on the total length the text in the cell. The formula then uses the MID function to extract the desired line. The starting point is worked out with: Where “N” stands for “nth line”, which is picked up from row 4 with the D$4 reference....
Alternative with EOMONTH The EOMONTH function returns the last day of in the month of a given date. This means you can get the first day of the current month with a formula like this: This formula “rolls back” a date in A1 to the last of the previous month, then adds 1. The result is the first day of the “current” month (i.e. first day of the month given by the date in A1)....
Where “date” is the named range D5:D14. Note: this is an array formula and must be entered with Control + Shift + Enter. The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today: When a result is TRUE, the date is passed into array returned by IF. When a result is FALSE, the date is replaced by the boolean FALSE....
The result is the close price for each symbol in the table on July 1, 2021. Note that if there is no close date on the date given, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page. STOCKHISTORY retrieves historical stock price information based on a given symbol and date range. For example, to return the closing price for Apple, Inc....
For example, if you have dates in the range B4:G11, and want to highlight dates that have the same month and year as the date June 1, 2015, select the range B4:G11 and create a new CF rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see all dates that occur in June 2015 highlighted....
In this example we’ll assign a simple macro that auto adjusts the column width to the data in the sheet. See my post about creating a macro to make your own. Step 1: Open the QAT options window. Add a macro to the QAT. Step 3: Assign your macro. Select the Macro you want to add to the QAT. Step 3: Run your macro from the QAT. Now you will be able to run your macro easily from the QAT....
For example, you can not (with 100% certainty) predict the temperature of the next 10 days or the stock price of a company in the coming week. There will always be a level of variability in the data. The final value could be a little higher or lower. If you have to represent this kind of data, you can use Error Bars in the Charts in Excel. What are Error Bars?...
A 100% stacked column chart is a sub type of a regular stacked column chart. The difference is that the 100% stacked version is meant to show the proportional breakdown of multiple data series in stacked columns, where the total height is always 100%. In this worksheet, we have quarterly sales data broken down by region and quarter. To start off, I’ll create a normal stacked column chart. I’ll flip the chart with the Switch Column/Rows button to group sales by quarter instead of by region....
Let’s take a look. When you first add Row or Column labels to a pivot table, you won’t see any subtotals. For example, in this pivot table, let’s add Category as a row label, and Region as a column label field, and Total Sales as a value. Notice we see grand totals, but no subtotals. However, if we add a second field to the Row labels area, Product, we’ll see a subtotal for each category appear as a row in the table....
Let’s take a look. Here we have a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa. Let’s say we want to add a second chart using the same data. We can easily copy and paste the existing chart to another location to create another copy. And, if we just want to change something like chart type, we can do that without affecting the other chart....
Suppose you have a list as shown above (which has repetitions) and you want to get unique items as shown on the right. Here is a combination of INDEX, MATCH and COUNTIF formulas that can get this done: When there are no more unique items, the formula displays an error. To handle it, I have used the Excel IFERROR function to replace the error message with a blank. Since this is an array formula, use Control + Shift + Enter instead of Enter....
Select all the sheets that you want to perform the action on. Now perform your action. In this example I insert a column into the table, add a column heading label of Region and add the region for each sales person. Now when you look at each sheet in the workbook all the tables have gotten this updated column. Wow like magic, what a time saver. Tip: If your workbook has a lot of sheets and you want a quicker way of selecting your sheets use this trick....
Here we have a list of 36 people. Let’s say we want to randomly assign each person to a team of 4 people so that we have a total of 9 teams with 4 people in each. I’m going to solve this problem in small steps, with helper columns, then bring things together in the end. This is a great way to solve more complicated problems in Excel. I’ll start with an Excel Table, to make the formulas very fast to enter....
where 70 represents the passing score for all subjects. The result in I5 is 3, in I6 is 5, and in I7 is 6. It’s a good idea to use COUNTIF alone as you start this formula, to make sure you are getting the results you expect before adding more logic. The number returned by COUNTIF is then checked against 4 with the greater than or equal to operator (>=), and the expression returns TRUE or FALSE for the logical test inside the IF function....
which rounds the value in B6 to the nearest whole dollar, then subtracts .01. For rounding, we use the ROUND function, with the num_digits argument set to zero (0) for no decimal places: The ROUND function with a zero will round to the nearest whole dollar. Once rounded, the formula simply subtracts 0.01 to get a .99 value. The formula in C6, copied down, is: With the value in B6 of 63....
