Posts

When there is no dedicated function to get the first day of a given month, you can easily use a workaround to find it. In this Excel tutorial, I’ll show you some really simple formula that you can use to get the first day of a month in Excel based on a given date. So let’s get started! Get the First Day of the Month Using EOMONTH EOMONTH Function gives you the last day of the month for a given date (hence called the end-of-month function)....

Inserting page numbers in Excel takes a little more work as compared with other Microsoft applications such as Word or PowerPoint. Watch Video – How to Insert Page Numbers in Excel How to Insert Page Numbers in Excel In this tutorial, you’ll learn how to insert page numbers in Excel worksheets. How to Insert Page Numbers in Excel using the using the Page Layout View. Insert Page Numbers in Excel Using the Page Setup Dialogue Box....

Many applications come with fonts that are automatically installed with the program. Microsoft Excel is no exception and comes with many fonts that you can use across all your spreadsheets. Although Excel comes with a large selection of fonts to choose from, you may want to install a custom font that you’ve either created yourself, or found elsewhere. Here’s how you can add new fonts to Excel. Free Custom Fonts Online One of the most important aspects of any document is its font....

When to use Excel SEARCH Function Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. What it Returns It returns a number that represents the starting position of the string you are finding in another string. Syntax =SEARCH(find_text, within_text, [start_num]) Input Arguments find_text – the text or string that you need to find. within_text – the text within which you want to find the find_text argument....

Let’s take a look. The fill handle is a special box that appears in the lower right-hand corner of a selection. When you hover over the fill handle, you’ll see the cursor change from a white selection cross to a thin black cross. When you see the black cross, click and drag the handle over the cells you’d like to fill. The fill handle can fill cells in four directions: right and left; up, down....

So, to start off, what’s a Boolean? A Boolean is a data type with only two possible values, TRUE or FALSE. You’ll often see Boolean results, or Boolean expressions in Excel. For example, if I enter the formula =B5>30 here, we’ll get the Boolean result of TRUE. This is a Boolean expression—or logical statement—that returns either TRUE or FALSE. If I copy the formula down, we’ll get a Boolean result for every number in the list....

In the example shown, the formula in F5 is: Note: this is an array formula and must be entered with control + shift + enter. In this example, we need include only values associated with group B. To do this, we use the IF function to filter: Since we are running a logical test on an range of cells, we get an array of results: {FALSE;98;FALSE;60;FALSE;95;FALSE} Note that only values in group B make it into the array....

The value in B6 is 1,234,567 and the result is 1,235,000. With the ROUND function, negative numbers for the second argument round to the left of the decimal and positive numbers round to the right of the decimal. In this case, by supplying -3, we are telling ROUND to round the number to the 3rd place on the left – the 1000’s place. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

where date (B5:B16), amount (C5:C16)), and FY (D5:D16)) are named ranges. When copied down, this formula returns sum of amounts for the fiscal years in F5 and F6 as shown, based on a fiscal start in July. Helper column To make the example easier to understand and to provide a simple way to use the SUMIF function (see below), column D is set up as a helper column that displays a fiscal year for each row, based on a July start....

In this tutorial, I will show you different ways to use IFERROR with VLOOKUP to handle these #N/A errors cropping up in your worksheet. Using the combination of IFERROR with VLOOKUP allows you to show something meaningful in place of the #N/A error (or any other error for that matter). Before getting into details on using this combination, let’s first quickly go through the IFERROR function and see how it works....

Let’s take a look. In this first worksheet, we have a list of ten properties set up in a normal way. If we check the formulas that summarize this data to the right, you can see that each formula refers to a standard range of cells. So, what happens if I add some more properties to this list? Let’s try it out and see. On the data worksheet, I have data for another six properties....

Data Validation is a very useful Excel tool. It often goes unnoticed as Excel users are eager to learn the highs of PivotTables, charts and formulas. It controls what can be input into a cell, to ensure its accuracy and consistency. A very important job when working with data. In this blog post we will explore 11 useful examples of what Data validation can do. To apply these Data Validation rules;...

The problem was that she needed to display the name, street, city, country and post code each on a separate line within one cell. In this post we’re going to take a look at five different ways we can concatenate the data and separate each item with line breaks. In other words, we will combine or join data from multiple cells into one cell and separate them with line breaks....

And just like every other thing in Excel, there are multiple ways to insert columns as well. You can insert one or more single columns (to the right/left of a selected one), multiple columns (adjacent or non-adjacent), or a column after every other column in a dataset. Each of these situations would need a different method to insert a column. Insert New Columns in Excel In this tutorial, I will cover the following methods/scenarios to insert new columns in Excel:...

It’s a great new tool from Microsoft that allows you to quickly and easily create surveys, quizzes and polls. There is a wide variety of potential uses for these. You could use them to get customer feedback, collect reviews and testimonials or even use them as data entry forms. I’m using these using these forms in my courses to get student feedback and reviews so I can improve my teaching....

The data used to plot this chart is shown below: See also: Same data in a stacked column chart. How to make this chart At this point, you can finalize the chart by setting a title, and adjusting overall chart size, font size, and colors. 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....

The result is that the range B5:B16 and range D5:D16 are joined together horizontally into a single range. The CHOOSE function The CHOOSE function is used to select arbitrary values by numeric position. CHOOSE is a flexible function and accepts a list of text values, numbers, cell references, in any combination. For example, if we have the colors “red”, “blue”, and “green”, we can use CHOOSE like this: If we give CHOOSE an array constant like {1,2}, CHOOSE will return the first and second values in an array at the same time: The result is an array that contains two values and, in the dynamic array version of Excel, these values spill onto the worksheet into the range G5:H16....

where data is the named range B5:B15. The result is 3, since there are three cells in B5:B15 that contain numbers less than zero. COUNTIF function The COUNT function counts the number of cells in a range that match the supplied criteria. For example, you can use COUNTIF like this: To count negative numbers in this example, we need to use the less than operator (<) with zero like this: To include zero in the count, use the less than or equal to operator (>=): Notice that the criteria is enclosed in double quotes ("")....

The result is a text string that contains both the name and email address enclosed in angle brackets (<>). In the worksheet shown, column B contains a name, and column C contains an email address. The formula in column D uses the ampersand character (&) to join the name and email address together: This is an example of concatenation. On the right side of the formula, the email address in C5 is wrapped in angle brackets: Notice the angle brackets are text and must be enclosed on double quotes ("")....

The first time a customer appears in the list, the formula returns 1. Subsequent occurrences return zero. Because the first reference is absolute and the second reference is relative, the range expands as the formula is copied down the column. The criteria is simply the value in the current row of column B. COUNTIFS returns the count of the current customer up to that point in the data. This means the first occurrence of a customer is 1, the second is 2, and so on....