# data[].attributes.body
Watch the video to gain an understanding of the basic elements of Word. Video explains the word environment to include Tabs, Ribbons and Groups

# data[].attributes.attachment_link.title
Beginning Graphic Design: Layout & Composition

# data[].attributes.attachment_link.title
Free Basic Computer Skills Tutorial at GCFGlobal

# data[].attributes.attachment_link.description
If you are looking for basic computer skills or to upgrade computer skills you already have, review our free Basic Computer Skills tutorial.

# data[].attributes.headline
Interactive Guide to help you as a beginner

# data[].attributes.subject
Interactive Guide to help you as a beginner

# data[].attributes.body
Link to a webpage created by learners on 1st Digital Marketing course that lists useful resources.

# data[].attributes.attachment_link.readable_text
Below is a table with the Excel sample data used for many of my web site examples. You can use this sample data to create test files, and build Excel tables and pivot tables from the data. Copy and paste from this table, or download the sample data file.

If you need more variety in your Excel sample data, go to the More Sample Data Files section below. There are sample files with property insurance data, food sales records, and hockey player data.

There are hundreds more sample Excel files that you can download, and Excel lessons, with videos and written steps.

Sample Data

To use this office supply sales sample data:

Sample Data Notes

The sample data on this page is sales data for an imaginary office supply company, and each row represents one order. Each row shows:

OrderDate: when the order was placed,

Region: geographical area in which the sale was made

Rep: sales representative's name

Item: name of the item sold

Units: number of units sold

UnitCost: cost of one unit

Total: total cost of the order - Units x UnitCost

Get the Sample Data

To use this sample data, download the sample file, or copy and paste it from the table on this page.

Download the Sample File

To download the sample data in an Excel file, click this link: Excel sample data workbook

The zipped file is in xlsx format, and does not contain any macros

NOTE: The Total column contains values. You could change it to a formula, to multiply the Units and Cost columns.

Copy and Paste

Click at the end of the "Sample Data" heading, above the table (you won't see anything happen)

Scroll down to the end of the sample data table

Press Shift, and click at the end of the last number in the table, to select all the data

Press Ctrl + C to copy the data

Paste it into an Excel workbook, for use in your own tests.

Create an Excel Table

After you paste the sample data into Excel, you can format it as a named Excel table. This will make it easier to sort and filter the data.

Watch this short video to see the steps, and there are written instructions on the Creating an Excel Table page.

Sample Data - Formatted Numbers

If you copy numbers such as 1-4 or 3/5 and paste them into Excel, they will usually change to dates.

For example, copy the numbers below, and paste them onto a worksheet, to see how Excel adjusts them.

Learn how to paste this type of data, and keep the formatting -- instructions on the Data Entry Tips page.

More Sample Data Files

Here are 4 more sample data files, if you'd like a bit of variety in your Excel testing.

Insurance Policies

This sample file has fake commercial property insurance policy data.

There are 10 columns of data, with no calculations.

There are 500 rows of data in the insurance policy table.

Click here to download the insurance policy data file. The zipped Excel file is in xlsx format, and does not contain any macros.

Food Sales

This sample file has fake food sales data.

There are 8 columns of data, including 1 column with a calculation.

There are 244 rows of data in the food sales table.

Click here to download the food sales data file. The zipped Excel file is in xlsx format, and does not contain any macros.

Workplace Safety

This sample file has fake workplace safety report data.

There are 14 columns of data, including 3 columns with a calculation.

There are 514 rows of data in the workplace safety report table.

Click here to download the workplace safety data file. The zipped Excel file is in xlsx format, and does not contain any macros.

Hockey Players

This sample file has data from the 2018 Olympic Hockey teams, from Canada and the USA. I used this data in my blog post on hockey player data analysis.

There are 15 columns of data, including 4 columns with calculations.

There are 96 rows of data in the hockey player table.

Click here to download the hockey player data file. The zipped Excel file is in xlsx format, and does not contain any macros.

Related Links
Excel Tables Pivot Tables Data Entry Tips More Sample Files Excel Topics
Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

_____________________

Last updated: November 24, 2020 4:05 PM

# data[].attributes.attachment_link.description
Below is a table with the Excel sample data used for many of my web site examples. You can use this sample data to create test files, and build Excel tables and pivot tables from the data. Copy and paste from this table, or download the sample data file.

# data[].attributes.attachment_link.readable_text
Numeric Criteria | Text Tricks | Count Booleans | Count Errors | And Criteria | Or Criteria | More about Countif | Count Magic

The powerful COUNTIF function in Excel counts cells based on one criteria. This page contains many easy to follow COUNTIF examples.

Numeric Criteria

Use the COUNTIF function in Excel to count cells that are equal to a value, count cells that are greater than or equal to a value, etc.

1. The COUNTIF function below counts the number of cells that are equal to 20.

2. The following COUNTIF function gives the exact same result.

3. The COUNTIF function below counts the number of cells that are greater than or equal to 10.

4. The following COUNTIF function gives the exact same result.

Explanation: the & operator joins the 'greater than or equal to' symbol and the value in cell C1.

5. The COUNTIF function below counts the number of cells that are not equal to 7.

6. The COUNTIF functions below count the number of cells that are equal to 3 or 7.

Text Tricks

Use the COUNTIF function in Excel and a few tricks to count cells that contain specific text. Always enclose text in double quotation marks.

1. The COUNTIF function below counts the number of cells that contain exactly star.

2. The COUNTIF function below counts the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

3. The COUNTIF function below counts the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

4. The COUNTIF function below counts the number of cells that contain star in any way.

5. The COUNTIF function below counts the number of cells that contain text.

Count Booleans

Use the COUNTIF function in Excel to count Boolean values (TRUE or FALSE).

1. The COUNTIF function below counts the number of cells that contain the Boolean TRUE.

2. The COUNTIF function below counts the number of cells that contain the Boolean FALSE.

Count Errors

Use the COUNTIF function in Excel to count specific errors.

1. The COUNTIF function below counts the number of cells that contain the #NAME? error.

2. The array formula below counts the total number of errors in a range of cells.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Counting Errors for detailed instructions on how to create this array formula.

And Criteria

Counting with And criteria in Excel is easy. The COUNTIFS function (with the letter S at the end) in Excel counts cells based on two or more criteria.

1. For example, to count the number of rows that contain Google and Stanford, simply use the COUNTIFS function.

Or Criteria

Counting with Or criteria in Excel can be tricky.

1. The COUNTIF functions below count the number of cells that contain Google or Facebook (one column). No rocket science so far.

2. However, if you want to count the number of rows that contain Google or Stanford (two columns), you cannot simply use the COUNTIF function twice (see the picture below).

Note: rows that contain Google and Stanford are counted twice, but they should only be counted once. 4 is the answer we are looking for.

3. The array formula below does the trick.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Counting with Or Criteria for detailed instructions on how to create this array formula.

More about Countif

The COUNTIF function is a great function. Let's take a look at a few more cool examples.

1. The COUNTIF function below uses a named range. The named range Ages refers to the range A1:A6.

2. The COUNTIF function below counts the number of cells that are less than the average of the ages (32.2).

3. To count cells between two numbers, use the COUNTIFS function (with the letter S at the end).

4. Use the COUNTIF function to count how many times each value occurs in the named range Ages.

Note: cell B2 contains the formula =COUNTIF(Ages,A2), cell B3 =COUNTIF(Ages,A3), etc.

5. Add the IF function to find the duplicates.

Tip: use COUNTIF and conditional formatting to find and highlight duplicates in Excel.

Count Magic

The COUNTIF function can't count how many times a specific word occurs in a cell or range of cells. All we need is a little magic!

1. The formula below counts how many times the word "dog" occurs in cell A1.

2. The formula below counts how many times the word "dog" occurs in the range A1:A2.

Note: visit our page about counting words to learn more about these formulas.

# data[].attributes.attachment_link.description
Numeric Criteria | Text Tricks | Count Booleans | Count Errors | And Criteria | Or Criteria | More about Countif | Count Magic The powerful COUNTIF function in Excel counts cells based on one criteria. This page contains many easy to follow COUNTIF examples.

# data[].attributes.body
Word Sample Test

# data[].attributes.attachment_link.readable_text
Freeze Top Row | Unfreeze Panes | Freeze First Column | Freeze Rows | Freeze Columns | Freeze Cells | Magic Freeze Button

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

Freeze Top Row

To freeze the top row, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

2. Click Freeze Top Row.

3. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a dark grey horizontal line to indicate that the top row is frozen.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

2. Click Unfreeze Panes.

Freeze First Column

To freeze the first column, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

2. Click Freeze First Column.

3. Scroll to the right of the worksheet.

Result. Excel automatically adds a dark grey vertical line to indicate that the first column is frozen.

Freeze Rows

To freeze rows, execute the following steps.

1. For example, select row 4.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll down to the rest of the worksheet.

Result. All rows above row 4 are frozen. Excel automatically adds a dark grey horizontal line to indicate that the first three rows are frozen.

Freeze Columns

To freeze columns, execute the following steps.

1. For example, select column E.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll to the right of the worksheet.

Result. All columns to the left of column E are frozen. Excel automatically adds a dark grey vertical line to indicate that the first four columns are frozen.

Freeze Cells

To freeze cells, execute the following steps.

1. For example, select cell C3.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll down and to the right.

Result. The orange region above row 3 and to the left of column C is frozen.

Magic Freeze Button

Add the magic Freeze button to the Quick Access Toolbar to freeze the top row, the first column, rows, columns or cells with a single click.

1. Click the down arrow.

2. Click More Commands.

3. Under Choose commands from, select Commands Not in the Ribbon.

4. Select Freeze Panes and click Add.

5. Click OK.

6. To freeze the top row, select row 2 and click the magic Freeze button.

7. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a dark grey horizontal line to indicate that the top row is frozen.

Note: to unlock all rows and columns, click the Freeze button again. To freeze the first 4 columns, select column E (the fifth column) and click the magic Freeze button, etc.

# data[].attributes.attachment_link.description
Freeze Top Row | Unfreeze Panes | Freeze First Column | Freeze Rows | Freeze Columns | Freeze Cells | Magic Freeze Button If you have a large table of data in Excel, it can be useful to freeze rows or columns.

# data[].attributes.attachment_link.readable_text
Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

For example:

=COUNTIF(A2:A5,"London")

=COUNTIF(A2:A5,A4)

COUNTIF(range, criteria)

Argument name

Description

range (required)

The group of cells you want to count. Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored.

Learn how to select ranges in a worksheet.

criteria (required)

A number, expression, cell reference, or text string that determines which cells will be counted.

For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples".

COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data

Data

Formula

Description

=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)

Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.

Common Problems

Problem

What went wrong

Wrong value returned for long strings.

The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.

To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,"long string"&"another long string").

No value returned when you expect a value.

Be sure to enclose the criteria argument in quotes.

Best practices

Do this

Why

Be aware that COUNTIF ignores upper and lower case in text strings.

Criteria aren't case sensitive. In other words, the string "apples" and the string "APPLES" will match the same cells.

Use wildcard characters.

Wildcard characters -the question mark (?) and asterisk (*)-can be used in criteria. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.

For example, =COUNTIF(A2:A5,"apple?") will count all instances of "apple" with a last letter that could vary.

Make sure your data doesn't contain erroneous characters.

When counting text values, make sure the data doesn't contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value.

Try using the CLEAN function or the TRIM function.

For convenience, use named ranges

COUNTIF supports named ranges in a formula (such as =COUNTIF( fruit,">=32")-COUNTIF( fruit,">85"). The named range can be in the current worksheet, another worksheet in the same workbook, or from a different workbook. To reference from another workbook, that second workbook also must be open.

Note: The COUNTIF function will not count cells based on cell background or font color. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font color. Here is an example of how you can Count the number of cells with specific cell color by using VBA.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See also

To count cells that aren't blank, use the COUNTA function

To count cells using multiple criteria, use the COUNTIFS function

See a video on how to use the COUNTIFS function

The SUMIF function adds only the values that meet a single criteria

The SUMIFS function adds only the values that meet multiple criteria

IFS function (Microsoft 365, Excel 2016 and later)

Use the TRIM function to remove leading and trailing spaces from cells

Use the CLEAN function to remove non-printing characters from cells

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Statistical functions

Excel functions (alphabetical)

Excel functions (by Category)

Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)

# data[].attributes.attachment_link.description
Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list. In its simplest form, COUNTIF says: For example: =COUNTIF(A2:A5,"London") =COUNTIF(A2:A5,A4) COUNTIF(range, criteria) Argument name Description range (required) The group of cells you want to count.

# data[].attributes.body
Excel Sample Test

# data[].attributes.body
Powerpoint Sample Test

# data[].attributes.body
Word module. Download these files and use them while working through the Word Manual