Excel Tutorials for beginners, Intermediates and experts.
This tutorial shows how to Sum by group in Excel using the example below; Formula =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Explanation To subtotal data by group or label, directly in a table, you can use a formula based on the SUMIF function. In the example shown, the formula in D5 is: =IF(B5=B4,””,SUMIF(B:B,B5,C:C)) Note: data must be sorted by the …
This tutorial shows how to Subtotal invoices by age in Excel using the example below; Formula =SUMIF(age,criteria,amount) Explanation If you need to subtotal invoice amounts by age, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: =SUMIF(age,G5,amount) Where age is a named range for the range …
This tutorial shows how to Subtotal by color in Excel using the example below; If you need to subtotal numbers by color, you can easily do so with the SUMIF function. Formula =SUMIF(color_range,criteria,number_range) Explanation In the example shown, the formula in G5 is: =SUMIF($B$4:$B$11,F5,$D$4:$D$11) How this formula works The SUMIF function takes three arguments: range, …
This tutorial shows how to Count cells that do not contain errors in Excel using the example below; Formula =SUMPRODUCT(–NOT(ISERR(range))) Explanation To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the example, the active cell contains this formula: =SUMPRODUCT(–NOT(ISERR(B4:B8))) How this …
This tutorial shows how to Count cells that contain errors in Excel using the example below; Formula =SUMPRODUCT(–ISERR(range)) Explanation To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula: =SUMPRODUCT(–ISERROR(B5:B9)) How this formula works SUMPRODUCT accepts one or more arrays, multiplies …
This tutorial shows how to Count cells less than in Excel using the example below; Formula =COUNTIF(range,”<X”) Explanation To count the number of cells that contain values less than a particular number, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers, …
This tutorial shows how to Count cells that do not contain many strings in Excel using the example below; Formula {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} Explanation To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is: {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE …
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names. 1. Select the range with full names. 2. On the Data tab, in the Data Tools group, click Text to Columns. …
How do you know which paper size when you want to print in Excel? | Selecting a Paper Size Working excel files is one thing, knowing what paper size to print completed work is another. In most cases, you’ll use the standard 8.5-x-11-inch letter-sized paper to print your worksheets. However, if you find that you …
Understanding Cell Ranges, Auto fills, Copy/Paste, Insert Row, Column in Excel Auto fill options in excel is to cell based on another or fill column with same value without dragging CELL Ranges People find it difficult to understand Cell Ranges in Excel. To effectively work with formulas it is apparent to have a good knowledge …
A workbook is another word for your Excel file as referred by Microsoft.. And, also a workbook comprises of several worksheets depending on the user. When you start Excel, click Blank workbook to create an Excel workbook from scratch. Open an Existing Workbook To open a workbook you’ve created in the past, execute the following steps. 1. On the File …
How To: Select, Insert, Rename, Move, Delete A Worksheet From A Workbook A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets. Select a Worksheet in Excel When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet …
Excel can fit three more columns of information on the page in landscape mode than it can in portrait mode. By default, paper orientation is mostly portrait. This article shows how to change paper orientation in Excel before printing a worksheet or workbook. Print a worksheet in landscape or portrait orientation Steps to change page …
Detailed steps on how to use Excel Find, Select, Replace & Go To Features You can use Excel’s Find and Replace feature to quickly find specific text and replace it with other text. You can use Excel’s Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc. Find To quickly find …
Templates are predefined layouts, patterns, structures built that can be reused. Microsoft office Excel comes with multiple in built Templates. If you often use the same layout or data, instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used. Existing Templates …
Excel obviously allows you to print your worksheets to a printer. Sometimes a worksheet will fit on a single page, but other times it will flow to another page. These steps teaches you how to print a worksheet and how to change some important print settings in Excel such as: Print a Worksheet What to …
Why do you need to share data in another file format? Most of the time, you’ll simply need to paste static Excel data in a Word document Other times you may want to paste data and then click Linked to Worksheet Object. Screenshots on how to share Excel data with Word documents and other files 1. …
Limit Others From Accessing Excel Workbook by Adding Password To Your File. Encrypt an Excel file with a password so that it requires a password to open it. A password protect an excel file, lock an excel spreadsheet from editing but allow read-only. Steps To Password Protect a Workbook in Excel 1. Open a workbook. 2. On the File …
A good knowledge of keyboard shortcuts for Excel helps user work more efficiently. The Excel shortcut keys make us work 10 times faster than using the normal conventional way to solve office challenges. Sometimes, computer users wonder how proficient their colleagues are in using Microsoft Office Excel without the aide of mouse. This post is …
This lesson illustrates a quick and easy way to enter data in Excel using the fill handle. The fill handle is used to automatically fill data in series in a worksheet e.g serial Number, Days of the Week, Months of the year, Date and Time, etc. 1. For example, enter the value 10 into cell …
Quick Tips On How To Set Multiple Print Areas in Excel A print area is one or more ranges of cells that you designate to print when you don’t want to print the entire worksheet. If you print a specific selection on a worksheet frequently, you can define a print area that includes just that selection. …
Cell references in Excel are very important. Building a structure or a template in excel using formula one needs to understand the difference between relative, absolute and mixed reference. Relative Reference To identify relative referencing, it is simply the ‘cell name’ as illustrated below. N/B: Cell Name comprises of a column label and a row number of …
It could be challenging sometimes working in excel and you find ##### error, #NAME? error , #VALUE! error , #DIV/0! error and #REF! error on your worksheet. This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide enough to display the value. 1. Click …
Array Formulas: Without Array Formula, With Array Formula and F9 Key This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. With Array Formula We don’t need to store the range in column D. Excel can store this range in its memory. A range stored in Excel’s memory is called …
Use flash fill in Excel 2013 or later to automatically extract or combine data. Join 1. For example, use flash fill to join the last names in column A and the first names in column B to create email addresses. 2. First, tell Excel what you want to do by entering a correct email address in cell C1. 3. …
To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on …
To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. Formula =CHOOSE(RANDBETWEEN(1,4),num1,num2,num3,num4) Explanation In the example shown, the formula in B4 is: =CHOOSE(RANDBETWEEN(1,4),25,50,75,100) Which returns a random number from the numbers provided. How this formula …
To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control + …
To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, …
To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if …
To add sequential row numbers to a set of data with a formula, you can use the ROW function. See example below: Formula =ROW()-offset Explanation In the example shown, the formula in B5 is: =ROW()-4 How this formula works When not given a reference, the ROW function returns the row number of the current row. …
If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. Formula =ROWS(range)*COLUMNS(range) Note that this formula only works for a single rectangular range of cells. Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)*COLUMNS(B4:C9) How this formula works There …
If you need to count the number of columns in a range, use the COLUMNS function. COLUMNS counts the number of columns in and returns the result as a number. Formula =COLUMNS(range) Explanation In the example, the active cell contains this formula: =COLUMNS(B4:C9)
To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you …
To dynamically sort data that contains only numeric values, you can use a helper column and a formula created with the RANK and COUNTIF functions. Formula =RANK(A1,values)+COUNTIF(exp_rng,A1)-1 Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here. Explanation In the example shown, the …
To catch errors that a formula might trigger in a worksheet, you can use the IFERROR function to display a custom message, or nothing at all. See example below: Formula =IFERROR(formula,value_if_error) Explanation In the example shown, the formula in E5 is: =IFERROR(C5/D5,””) How this formula works In this example, the IFERROR function is used to …
To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. See example below: Formula =SUBSTITUTE(ADDRESS(1,col_number,4),”1″,””) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) The first step is to construct an address that contains the column …
To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions. See example below: Formula =COLUMN(INDIRECT(letter&”1″)) Explanation In the example shown, the formula in C5 is: =COLUMN(INDIRECT(B5&”1″)) How this formula works The first step is to construct a standard “A1” …
This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. …
To generate random integers between two numbers, you can use the RANDBETWEEN function. Formula =RANDBETWEEN(lower,upper) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is random numbers between 1-100. Note that the RANDBETWEEN function will generate new numbers whenever a change …