I regularly use to-do lists and calendars to plan my days in advance. In this post, I am sharing an Excel calendar template that you can use in Excel or even take a printout and stick it at home or at your workplace. While it’s easy to create a static calendar in Excel, I have used the power of Excel formulas to create a dynamic calendar template that will update based on the selections you make....
The result is the latest available close price in the current month. If there is no data yet in the current month, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page. The STOCKHISTORY function retrieves historical stock price information based on a given symbol and date range. For example, to return the closing price for Apple, Inc. on December 27, 2021, you can use: The result is an array that includes headers, date, and close price:...
The result is detailed historical pricing for Microsoft Corporation (“MSFT”) over the last 30 days. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The data appears in reverse chronological order because the STOCKHISTORY is nested inside the SORT function, which is configured to sort in descending order. This can be done with the STOCKHISTORY function, whose main purpose is to retrieve historical information for a financial instrument over time....
It’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Also note that because we are using less than (<) and greater than (<), the lower and upper limit will not be included. Using other cells as inputs You don’t have to hard-code the numbers into the rule and, if the numbers will change, it’s better if you don’t....
Let’s take a look. If you need to apply subscript or superscript formatting, you won’t find these options on the ribbon. Instead, they’re located on the Font tab of the Format Cells dialog box in the Effects group. To apply either subscript or superscript, you need to follow a particular process. You can’t just select a cell normally and apply one of these formats. If you do, you’ll see that all text in a cell is affected, which is almost certainly not what you want....
What is Compound Interest? Let me take a simple example to explain it. Suppose you invest USD 1000 in a bank account that promises to give you 10% return at the end of the year. So at the end of year 1, you get USD 1100 (1000+100). Now since you didn’t have any immediate use of the money, you let it stay in the account. And the bank did its part and added 10% at the end of the year....
Let’s take a look. Let’s continue with our menu project and add some font colors. Let’s make the headings one color and the menu items another. Even though we want the headings to be a different color, it will be easiest if we apply one color to the entire worksheet, and then come back and work on the headings separately. As always, start by selecting the cells to format. The easiest way to apply font color is to use the Font color menu on the home tab of the ribbon....
Change the default setting.
CHAR(N) for N = 65 to 90 will result in the characters A to ZCHAR(N) for N = 97 to 122 will result in the characters a to zCHAR(N) for N = 33 to 47 will result in the characters ! ” # $ % & ‘ ( ) * + , – . /CHAR(N) for N = 58 to 64 will result in the characters : ; < = > ?...
Example Generic Formula What It Does This formula will return the percent of the year completed for a given date. How It Works The YEARFRAC function will take two dates and return the fraction of the year between them. We will use this function with the our given date and the previous December 31st date to get the desired fraction of the year. We can determine what the previous December 31st date is by using the DATE and YEAR functions....
Let’s take a look. Here we have the same pivot table we looked at earlier. Sales are summarized by Region, and we are using a calculated item, Eastern, to summarize the regions East and South. Before we make any changes, let’s add Product as a Row Label above the Region field. Now we have multiple instances of the calculated item Eastern. As you can see, the Name of a calculated item appears directly in the pivot table....
Sometimes when working with data in Excel, you may have a need to move rows and columns in the dataset. For example, in the below dataset, I want to quickly move the highlighted row to the top. Now, are you thinking of copying this row, inserting the copied row where you want it, and then deleting it? If yes – well that’s one way to do this. But there is a lot faster way to move rows and columns in Excel....
Or if you’re a keyboard person, you can hold the shift key and move the cursor to make the selection. But both of these methods work when you want to select a contiguous range of cells. But what if you want to select non-adjacent cells in Excel? You can do that too… it’s not as easy as selecting the adjacent cells, but it’s still quite easy. In this Excel tutorial, I will show you some simple ways to select non-adjacent cells in Excel....
When you use Excel frequently, you’re likely to see a number of potential errors flagged on the worksheet as you work. Exactly which errors are flagged depends on which error checking rules are enabled. To confirm, navigate to: options > formulas > error checking rules Note that these are global settings. If you disable a rule here, it will stay disabled for all worksheets until reenabled. When Excel flags an error on a worksheet, it will appear as a small triangle in the upper left of a cell....
Usually, the names are either in the ‘First Name Last Name’ format (such as Joe Davis or Sumit Bansal). In some specific situations, you may want the names data to be formatted as Last Name, First Name (i.e., last name followed by a comma and then the first name) In this tutorial, I will show you how to switch the first and the last name in Excel with a comma in between (when you have your data in the First Name Last Name format)...
Let’s look at some examples. Arguments can be required or optional. Some functions take three or more arguments, and some functions don’t take any arguments at all. A good example of a function that takes no arguments is the TODAY function, which returns the current date. To use it, just enter an equal sign, TODAY, and empty parentheses. Other functions take multiple arguments; all of which are required. A good example is the DATE function, which allows you to build a valid date using the year, month, and day supplied as separate and required arguments....
Let’s take a look. There are several ways to get to slicer options. First, you can click the Option tab of the Slicer Tools ribbon. The Slicer Tools ribbon will appear whenever you select a slicer. The Slicer Tools ribbon gives you direct access to slicer settings, slicer styles, tools for arranging slicers, and tools for configuring column count, button size, and slicer dimensions. You can set the heading or caption for a slicer directly on the ribbon itself....
This is extremely valuable in many situations as we will see in the examples later in this tutorial. To give you a simple example, suppose you have a list of grades in Excel and you want to highlight all those students who have scored an A. Now, if I ask you to do this manually, you will check each student’s grade and if it’s an A, you’ll highlight it, and if it isn’t, then you’ll leave it as is....
which returns the maximum temperature on the date in H7. Note: this is an array formula and must be entered with Control + Shift + Enter Inside the IF function, logical test is entered as B5:B9391=H7. Because we’re comparing the value in H7 against a range of cells (an array), the result will be an array of results, where each item in the array is either TRUE or FALSE. The TRUE values represent dates that match H7....
In the example shown, the formula in C6 is Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11 provided for holidays. WORKDAY then calculates the next business day one day in the future, taking into account holidays and weekends. If you need more flexibility with weekends, you can use WORKDAY....
