The basic elements of Excel use the following functions: SUM, COUNT, AVERAGE, TIME & DATE. With this tutorial, you will be able to perform calculations and keep time records.

Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.

Important: The calculated results of formulas (e.g. how to subtract numbers) and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

Let’s propose you want to confirm how many inventory items are unprofitable (subtract profitable items from total inventory). Alternatively, perhaps you must know how many employees are reaching retirement age (subtract the number of employees under 55 from total employees).

What do you want to do?

There are multiple ways to subtract numbers, including:

Firstly, to do basic subtraction, use the – (minus sign) arithmetic operator.

For example, if you input the formula =10-5 into a cell, the cell will present 5 as the result.

Subtract numbers in a range

Adding a negative number is equivalent to subtracting one number from another. Use the SUM function to add negative numbers in a range.

Note: No SUBTRACT function exists in Excel. Use the SUM function and convert any desired numbers you want to subtract to their negative values. For example, SUM(100,-32,15,-6) returns 77.

Example

Follow these steps to subtract numbers in various ways:

Firstly, choose all of the rows in the table below. Then, press CTRL-C on your keyboard.

Data

15000

9000

-8000

Formula

=A2-A3

Subtracts 9,000 from 15,000 (which equals 6,000).

-SUM(A2:A4)

Adds every number in the list, even negative numbers (net result is 16,000).

Secondly, in the worksheet, pick cell A1. Then, press CTRL+V.

Finally, to change between viewing the results and viewing the formulas, press CTRL+` (grave accent) on your keyboard. Alternatively, press the Show Formulas button (on the Formulas tab).

Using the SUM function

The SUM function totals all the numbers that you set as arguments. Most importantly, each argument can be a range, a cell reference, an array, a constant, a formula, or the result from a different function. For instance, SUM(A1:A5) adds all the numbers in the range of cells A1 to A5. Another example is SUM(A1, A3, A5) which adds the numbers that are occupied in cells A1, A3, and A5 (A1, A3, and A5 are arguments).

Often percentages can be frustrating because it’s sometimes challenging to remember what we learned about them in school. Using Excel helps you to learn how to calculate percentages of numbers for you – simple formulas can assist you in finding the percentage of a total, for example, or the percentage difference between two numbers.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011.

Windows

Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Find the percentage of a total

Imagine that your company sold $125,000 this quarter, and you must discover what percentage $20,000 is of the total.

To check, divide $20,000 by $125,000. Here’s the formula in cell C2: =B2/A2. The result is displayed as 0.16 because cell C2 is formatted differently compared to a percentage.

To format 0.16 as a percentage, (which will also delete the zero) on the Home tab, select the Percentage button. If you are using Excel for the Web, press Home > Number Format > Percentage.

A company sold $485,000 in 2011 and $598,634 in 2012. What’s the percent change between the two years?

Firstly, select cell B3 to import the Percentage format to the cell. On the Home tab, choose the Percentage button. If you are using Excel for the Web, pick Home > Number Format > Percentage.

In cell B3, divide the second year’s sales ($598,634.00) by the first year ($485,000.00), and then subtract 1.

Here’s the formula in cell C3. =(B2/A2)-1. The percentage of change between the two years is 23%. Pay attention to the parentheses around (B2/A2). Excel calculates what’s in parentheses first, and then subtracts the 1.

Consider that you have to sum values with multiple conditions, like the sum of product sales in a certain region. This is a convenient time for using the SUMIFS function in a formula.

Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013.

Check out this example in which we find ourselves with two conditions: we want the sum of Meat sales (from column C) in the South region (from column A).

Here’s a formula you can use to achieve this:

=SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)

As a result, you get the value 14,719.

Let’s inspect more closely at each element of the formula.

Breaking SUMIFS down – Step 1

=SUMIFS is an arithmetic formula. It calculates numbers, which in this case are in column D. Firstly, the first step is to clarify the location of the numbers:

=SUMIFS(D2:D11,

In other words, you intend to have the formula sum numbers in that column if they satisfy the conditions. That cell range is the first argument in this formula—the first aspect of data that the function must have as input.

Step 2

Secondly, you are looking to locate data that meets two conditions, so you type your first condition by defining the data’s location for function (A2:A11) and also what the condition is—which is “South”. Most importantly, be aware of the commas between the separate arguments:

=SUMIFS(D2:D11,A2:A11,”South”,

Quotation marks around “South” indicate that this is text data.

Step 3

Finally, you state the arguments for your second condition – the range of cells (C2:C11) that includes the word “meat,” alongside the word itself (surrounded by quotes) so that Excel can match it. End the formula with a closing parenthesis ). Then, press Enter. The result, once more, is 14,719.

=SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)

As you enter the SUMIFS function in Excel, if you forget the arguments, help is right at your disposal. After you enter =SUMIFS(, Formula AutoComplete displays below the formula, with the list of arguments in their correct order.

Seeing the image of Formula AutoComplete and the list of arguments, in our example sum_range is D2:D11, the column of numbers you plan to sum; criteria_range1 is A2.A11, the column of data where criteria1 “South” is found.

As you type, the remainder of the arguments will emerge in Formula AutoComplete (not illustrated here); criteria_range2 is C2:C11, the column of data where criteria2 “Meat” situates.

If you select SUMIFS in Formula AutoComplete, an article launches to provide you more info.

Give it a try

If you want to play around with the SUMIFS function, here’s some sample data and a formula that applies the function.

You can work with sample data and formulas directly here, in this Excel for the Web workbook. Edit values and formulas, or insert your own values and formulas and watch the results change in real-time.

Copy all the cells in the table underneath, and paste into cell A1 in a new worksheet in Excel. You might want to extend column widths to have a better view of formulas.

Region

Salesperson

Type

Sales

South

Ito

Beverages

3571

West

Lannin

Dairy

3338

East

Makovec

Beverages

5122

North

Makovec

Dairy

6239

South

Jordan

Produce

8677

South

Lannin

Meat

450

South

Lannin

Meat

7673

East

Makovec

Produce

664

North

Lannin

Produce

1500

South

Jordan

Meat

6596

Formula

Description

Result

‘=SUMIFS(D2:D11,A2:A11, “South”, C2:C11,”Meat”)

Sums the Meat Sales in Column C in the South region in Column A (result is 14719).

=SUMIFS(D2:D11,A2:A11, “South”, C2:C11,”Meat”)

Notes:

Want further examples to sum values with multiple conditions? You’ll find more in the SUMIFS function article.

If you want to produce a total value for only one range based on a value in another range, use the SUMIF function.

Linking to other workbooks is a regular task in Excel, but often you may find yourself with a workbook containing links you struggle to locate despite Excel saying the contrary. There is no instant way to track down every external reference that is used in a workbook, although there are multiple manual methods you can use to uncover them. You must check in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series to find workbook links.

Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 Excel 2007.

Any Excel workbook you’ve linked to will include that workbook’s filename in the link with its .xl* file extension (like .xls, .xlsx, .xlsm), so a recommended method is to search for all references to the .xl partial file extension. If you’re linking to another source, you’ll have to decide the optimal search term to use.

Find links used in formulas

Press Ctrl+F to reveal the Find and Replace dialogue.

Select Options.

In the Find what box, type .xl.

Inside the Within box, pick Workbook.

In the Look in box, choose Formulas.

Press Find All.

In the list box that is presented, search in the Formula column for formulas that include .xl. In this case, Excel found several instances of Budget Master.xlsx.

To select the cell with an external reference, click the cell address link for that row in the list box.

Tip: Click any column header to sort the column, and group all of the external references together.

Find links used in defined names

On the Formulas tab, in the Defined Names group, select Name Manager.

Check each entry in the list, and look in the Refers To column for external references. External references include a reference to another workbook, such as [Budget.xlsx].

Tips:

Choose any column header to sort the column, and group all of the external references together.

You can group several items with the Shift or Ctrl keys and Left-click if you want to delete numerous items at once.

Find links used in objects, like text boxes or shapes

Press Ctrl+G, the shortcut for the Go To dialogue, then pick Special > Objects > OK. This will choose all objects on the active worksheet.

Press the Tab key to toggle between each of the chosen objects, and then scan the formula bar for a reference to another workbook, like [Budget.xlsx].

Find links used in chart titles

Select the chart title on your desired chart to check.

In the formula bar , check for a reference to another workbook, such as [Budget.xls].

Find links used in chart data series

Click your preferred chart to check.

On the Layout tab, in the Current Selection group, select the arrow beside the Chart Elements box, and then choose your sought data series to check.

In the formula bar , search for a reference to another workbook, like [Budget.xls] in the SERIES function.

Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

A fast and simple way to adding values in spreadsheets is through AutoSum. Firstly, choose an empty cell right below a column of data. Then, select AutoSum > Sum on the Formula tab. As a result, Excel will now capture the range to be added. (AutoSum can even work along the same row if you pick an empty cell to the right of the cells to be summed.)

AutoSum builds the formula for you, saving you from having to type at all. However, if you prefer entering the formula manually, see the SUM function.

Add based on conditions

Use the SUMIF function for any time you want to adding values in spreadsheets with one condition. For example, when you have to calculate the total sales of one product.

Use the SUMIFS function for when you want to sum values with several conditions. For instance, you may want to add up the total sales of a given product, within a certain sales region.

Import number formatting, like dates, currency, or fractions to cells in a worksheet. For instance, if you’re completing your quarterly budget, you can apply the Currency number format. This will present your numbers in a way to symbolise money.

Applies to: Excel for Microsoft 365, 2019, 2016, 2013, 2011, 2010 & 2007 (both Windows and Mac where applicable).

Alternatively, if you have a column of dates, you can clearly state that you prefer the dates to display as March 14, 2012, 14-Mar-12, or 3/14.

Using number formatting

Firstly, choose the cells with your particular numbers you have to format.

Secondly, press CTRL+1 (on Windows). Alternately, press Control+1, or Command+1 (on a Mac)

Thirdly, click the Number tab. Do this in the Format Cells popup window.

Finally, select your desired number format Category. Then, pick precise formatting changes by the right.

Tip: Do you have numbers appearing in your cells as #####? This likely means your cell is too narrow to display the full number.

Simply double-click the right border of the column including the cells with #####.

This will change the column width and row height to occupy the number. As well as this, you can drag the right border of the column to customise the size to your preferences.

Stop your numbers from automatically formatting

Often, you could type numbers into a cell—or transfer them from a different data source—however, Excel formats those numbers differently than from what you anticipate. If, for instance, you enter a number along with a slash mark (/) or a hyphen (-), Excel may assume you’re entering a date and set a Date format.

You can stop this automatic number formatting by defining the Text format to the cells.

This is simple to do:

Firstly, choose the cells with the required numbers to impede automatic formatting.

Secondly, for Windows, press CTRL+1. On a Mac, press Control+1, or Command+1.

Finally, on the Number tab, pick Text in the Category list.

Need more help?

Here are some ways to resolve your queries and/or make a suggestion to Excel.

Applies to: Publisher for Microsoft 365, Publisher 2019, Publisher 2016, Publisher 2013.

Often, you can work quicker by resting your fingers on the keyboard instead of alternating to the mouse or touch pad. You possibly have previously used shortcuts like Ctrl+C for Copy and Ctrl+S for Save, but you may be unaware of the remaining shortcuts. Here they are:

These shortcuts were launched with the Publisher ribbon. Some tabs are variable and display only once you’ve added or picked an object like a shape or a table. The double-letter shortcuts enable the use of keyboard shortcuts with contextual tabs.

To employ these, first choose the object, then press Alt, press the two letter context menu shortcut, and then tap the leftover keys, if any. For example, to access the Shape Effects menu and append a shadow to a shape you click the shape, press Alt, JD to enter the Drawing Tools – Format tab, SE to access the Shape Effects menu, S to pick the Shadow Gallery, and then tab across the shadow options to import the shadow to your shape.

Display the Find and Replace task pane, with the Find option selected

F3 or CTRL+F or SHIFT+F4

Display the Find and Replace task pane, with the Replace option selected

CTRL+H

Check spelling

F7

Display the Thesaurus task pane

SHIFT+F7

Display the Research task pane

ALT + click a word

Select all the text (If the cursor is in a text box, this selects all text in the current story; if the cursor is not in a text box, this selects all the objects on a page.)

CTRL+A

Make text bold

CTRL+B

Italicise text

CTRL+I

Underline text

CTRL+U

Make text small capital letters, or return small capital letters to upper and lower case

CTRL+SHIFT+K

Open the Font dialogue

CTRL+SHIFT+F

Copy formatting

CTRL+SHIFT+C

Paste formatting

CTRL+SHIFT+V

Turn Special Characters on or off

CTRL+SHIFT+Y

Return character formatting to the current text style

CTRL+SPACEBAR

Apply or remove subscript formatting

CTRL+=

Apply or remove superscript formatting

CTRL+SHIFT+=

Increase space between letters in a word (kerning)

CTRL+SHIFT+]

Decrease space between letters in a word (kerning)

CTRL+SHIFT+[

Increase font size by 1.0 point

CTRL+]

Decrease font size by 1.0 point

CTRL+[

Increase to the next size in the Font Size box

CTRL+SHIFT+>

Decrease to the next size in the Font Size box

CTRL+SHIFT+<

Centre a paragraph

CTRL+E

Align a paragraph on the left

CTRL+L

Align a paragraph on the right

CTRL+R

Align a paragraph on both sides (justified)

CTRL+J

Distribute a paragraph evenly horizontally

CTRL+SHIFT+D

Set newspaper alignment for a paragraph (East Asian languages only)

CTRL+SHIFT+J

Display the Hyphenation dialogue box

CTRL+SHIFT+H

Insert the current time

ALT+SHIFT+T

Insert the current date

ALT+SHIFT+D

Insert the current page number

ALT+SHIFT+P

Prevent the word from getting hyphenated if it occurs at the end of a line

CTRL+SHIFT+0 (zero)

Copy text formats

To do this

Press

Copy formatting from the selected text

CTRL+SHIFT+C

Apply copied formatting to text

CTRL+SHIFT+V

Copy, cut, paste or delete text or objects

To do this

Press

Copy the selected text or object

CTRL+C or CTRL+INSERT

Cut the selected text or object

CTRL+X or SHIFT+DELETE

Paste text or an object

CTRL+V or SHIFT+INSERT

Delete the selected object

DELETE or CTRL+SHIFT+X

Undo or redo an action

To do this

Press

Undo what you last did

CTRL+Z or ALT+BACKSPACE

Redo what you last did

CTRL+Y or F4

Nudge an object

To do this

Press

Nudge a selected object up, down, left, or right

Arrow keys

If the selected object has a cursor in its text, nudge the selected object up, down, left, or right

ALT+arrow keys

Layer objects

To do this

Press

Bring object to front

ALT+F6

Send object to back

ALT+SHIFT+F6

Snap objects

To do this

Press

Turn Snap to Guides on or off

F10, SHIFT+R, SHIFT+S, SHIFT+M

Select or group objects

To do this

Press

Select all objects on the page (If your cursor is in a text box, this selects all the text in a story)

CTRL+A

Group selected objects, or ungroup grouped objects

CTRL+SHIFT+G

Clear the selection from selected text

ESC

Clear the selection from a selected object

ESC

Select the object within the group — if that object contains selected text

If your publication is in Two-Page Spread view, these commands apply to the selected two-page spread. Otherwise, these apply only to the selected page.

To do this

Press

Display the Go To Page dialogue box

F5 or CTRL+G

Insert a page or a two-page spread. If you are creating a newsletter, it opens the Insert publication type Pages dialogue box

CTRL+SHIFT+N

Insert duplicate page after the selected page

CTRL+SHIFT+U

Move between pages

To do this

Press

Display the Go To Page dialogue box.

F5 or CTRL+G

Go to the next page

CTRL+PAGE DOWN

Go to the previous page

CTRL+PAGE UP

Switch between the current page and the master page

CTRL+M

Use the master page

To do this

Press

Switch between the current page and the master page.

CTRL+M

Show or hide boundaries or guides

To do this

Press

Turn Boundaries on or off.

CTRL+SHIFT+O

Turn Horizontal Baseline Guides on or off (not available in web view)

CTRL+F7

Turn Vertical Baseline Guides on or off (East Asian languages only—not available in web view)

CTRL+SHIFT+F7

Zoom

To do this

Press

Switch between the current view and the actual size

On Mac, find the Applications folder and choose Microsoft Teams.

On mobile, hit the Teams icon.

Log in with your Microsoft 365 username and password.

Note: Teams is included within Microsoft 365, so you must have a Microsoft 365 Apps for business or Enterprise licence to use it. For details, see How do I get access to Microsoft Teams?

Pick a team and channel

A team is a group of people, conversations, files, and tools — complete in a one-stop-shop. A channel is a conversation in a team, geared to a department, project, or topic.

The optimal way to learn more about how teams and channels work is to select a team and channel and begin searching!

Click Teams on the left side of the app and then select a team. If you’re absent from a team and seek to make one, see Onboard your teams.

Pick a channel and delve into the Conversations, Files, and other tabs. For further info, see Teams and channels.