Excel Shortcuts For Mac and Windows

Over 200 Excel Shortcuts For Mac and windows


This article provides over 200 excel shortcuts for mac and windows which are grouped in different categories according to functions. One major way to increase your productivity when working in an Excel model is to use Excel shortcuts, or shortcut keys.

These excel shortcut for mac and windows perform major functions that, when used instead of clicking in the toolbar, dramatically increase efficiency in speed. Imagine simply pressing two or three keys on the keyboard as opposed to moving your hand to the mouse, moving the mouse and clicking several times. There are thousands of keyboard shortcuts that can be used within Excel to increase productivity. These shortcuts can perform any functions as simple as navigation within the spreadsheet to filling in formulas, or grouping data.

Excel’s Basic terminology

Before diving into the excel shortcuts for mac and windows, it would be prudent to first discuss basic terminology around the elements of excel.

  • A cell is any of the many boxes with the Excel spreadsheet.
  • An active cell is the cell currently selected by Excel. There can only be one active cell.
  • A selection is the active cell, or a group of cells currently selected. If more than one cell is in the selection, the active cell will be highlighted in white, while the rest of the selection is highlighted in gray.
  • A column is a group of vertical cells, referenced in Excel by letters in ascending order from A to Z. Beyond column Z, Excel will repeat letters a second time. As such, the column directly after column Z is column AA, followed by column AB.
  • A row is a group of horizontal cells, referenced in Excel by integers in increasing order from 1 to n. The value n depends on the operating system and version of Excel.
  • Learn more excel basics.

Get Number Bond App

Types of data that exist within Excel

  • Text are pieces of data that utilize letters. Numbers can also be used in Text data. However, these numbers must be used in conjunction with letters, or must manually be set to text.
  • Numbers are pieces of data that utilize numbers exclusively. Unlike text type data which can use numbers, number type data cannot use letters.
  • Currency/accounting pieces of data that utilize numbers in conjunction with a currency marker.
  • Dates are pieces of data that denote a date and/or time. There are multiple formats for dates within Excel.
  • Percentage type data are a subset of numbered data that is converted into a percentage. These can be converted back into the number-type, and vice versa. Converting to number type from percentage will show the number as a decimal. For example, 89% will convert to 0.89.
  • Learn more about formulas here and here.

Below are over 200 Excel shortcuts for Mac and Windows. They are grouped into different categories. Enjoy.

Excel Shortcuts for Mac and Excel

Excel Shortcuts for mac and windows
Excel Shortcuts1

Excel Shortcuts for mac and windows
Excel Shortcuts 2

Excel Shortcuts for mac and windows
Excel Shortcuts 3

Excel Shortcuts for mac and windows
Excel Shortcuts 4

Excel Shortcuts for mac and windows
Excel Shortcuts 5

Excel Shortcuts for mac and windows
Excel Shortcuts 6

Excel Shortcuts for mac and windows
Excel Shortcuts 7

Excel Shortcuts for mac and windows
Excel Shortcuts 8

Excel Shortcuts for mac and windows
Excel Shortcuts 9

Excel shortcuts for mac and windows
Excel Shortcuts 10

Excel Shortcuts for mac and windows
Excel Shortcuts 11

Excel Shortcuts for mac and windows
Excel Shortcuts 12

Excel Shortcuts for mac and windows
Excel Shortcut 13

Excel shortcuts for mac and windows
Excel Shortcuts 14

Excel Shortcuts for mac and windows
Excel Shortcuts 15

Excel Shortcuts for mac and windows
Excel Shortcuts 16

Excel Shortcuts for mac and windows
Excel Shortcuts 17

The Excel 2016 Worksheet Basics

Introduction

Excel is a spreadsheet program that allows you to store, organize, and analyze information. While you may believe Excel is only used by certain people to process complicated data, anyone can learn how to take advantage of the program’s powerful features. Whether you’re keeping a budget, organizing a training log, or creating an invoice, Excel makes it easy to work with different types of data.

Getting to know Excel 2016

If you’ve previously used Excel 2010 or Excel 2013, then Excel 2016 should feel familiar. If you are new to Excel or have more experience with older versions, you should first take some time to become familiar with the Excel interface.

The Excel 2016 interface

When you open Excel 2016 for the first time, the Excel Start Screen will appear. From here, you’ll be able to create a new workbook, choose a template, and access your recently edited workbooks.

·        From the Excel Start Screen, locate and select Blank workbook to access the Excel interface.

Then you have the worksheet opened. Check the illustration below to get familiar with the Excel worksheet interface

Working with the Excel environment

The Ribbon and Quick Access Toolbar are where you will find the commands to perform common tasks in Excel. The Backstage view gives you various options for saving, opening a file, printing, and sharing your document.

The Ribbon

Excel 2016 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with several groups of commands. You will use these tabs to perform the most common tasks in Excel.      

Each tab will has one or more group

·     Some groups will have an arrow you can click for more options.

·         Click a tab to see more commands.
·         You can adjust how the Ribbon is displayed with the Ribbon Display Options.
Certain programs, such as Adobe Acrobat Reader, may install additional tabs to the Ribbon. These tabs are called add-ins.

To change the Ribbon Display Options

The Ribbon is designed to respond to your current task, but you can choose to minimize it if you find that it takes up too much screen space. Click the Ribbon Display Options arrow in the upper-right corner of the Ribbon to display the drop-down menu.
There are three modes in the Ribbon Display Options menu:
·         Auto-hide Ribbon: Auto-hide displays your workbook in full-screen mode and completely hides the Ribbon. To show the Ribbon, click the Expand Ribbon command at the top of screen.
·         Show Tabs: This option hides all command groups when they’re not in use, but tabs will remain visible. To show the Ribbon, simply click a tab.
·         Show Tabs and Commands: This option maximizes the Ribbon. All of the tabs and commands will be visible. This option is selected by default when you open Excel for the first time.
 

The Quick Access Toolbar

Located just above the Ribbon, the Quick Access Toolbar lets you access common commands no matter which tab is selected.
By default, it includes the Save, Undo, and Repeat commands. You can add other commands depending on your preference.
To add commands to the Quick Access Toolbar:
1.  Click the drop-down arrow to the right of the Quick Access Toolbar.
2.  Select the command you want to add from the drop-down menu. To choose from more commands, select More Commands.
3.  The command will be added to the Quick Access Toolbar.
 

How to use Tell me

The Tell me box works like a search bar to help you quickly find tools or commands you want to use.
1.  Type in your own words what you want to do.
2.  The results will give you a few relevant options. To use one, click it like you would a command on the Ribbon.

Worksheet views

Excel 2016 has a variety of viewing options that change how your workbook is displayed. These views can be useful for various tasks, especially if you’re planning to print the spreadsheet.
To change worksheet views, locate the commands in the bottom-right corner of the Excel window and select Normal view, Page Layout view, or Page Break view.

·         Normal view is the default view for all worksheets in Excel.
 
·         Page Layout view displays how your worksheets will appear when printed. You can also add headers and footers in this view.
 
·         Page Break view allows you to change the location of page breaks, which is especially helpful when printing a lot of data from Excel.
 

Backstage view

Backstage view gives you various options for saving, opening a file, printing, and sharing your workbooks.
To access Backstage view:
1.     Click the File tab on the Ribbon. Backstage view will appear.
 

Challenge!

1.  Open Excel 2016.
2.  Click Blank Workbook to open a new spreadsheet.
3.  Change the Ribbon Display Options to Show Tabs.
4.  Using the Customize Quick Access Toolbar, click to add New, Quick Print, and Spelling.
5.  In the Tell me bar, type the word Color. Hover over Fill Color and choose a yellow. This will fill a cell with the color yellow.
6.  Change the worksheet view to the Page Layout option.
7.  When you’re finished, your screen should look like this:
8.  Change the Ribbon Display Options back to Show Tabs and Commands.
9.  Close Excel and Don’t Save changes.
 

Number Bond: An Essential Strategy of Singapore Math

Number Bond: An Essential Strategy of Singapore Math

Number bonds show how numbers are split or combined. An essential strategy of Singapore maths, number bonds reflect the ‘part-part-whole’ relationship of numbers.

By Gbenga Ayanfe

WHAT IS A NUMBER BOND?

Number bonds let students split numbers in useful ways. They show how numbers join together, and how they break down into component parts. When used in year 1, number bonds forge the number sense needed for early primary students to move to addition and subtraction. As students progress, number bonds become an essential mental problem solving strategy.

HOW DO NUMBER BONDS WORK?
Number bonds are represented by circles connected by lines. The ‘whole’ is written in the first circle, while the ‘parts’ are in the adjoining circles.

 HOW TO TEACH NUMBER BONDS?
Children are usually introduced to number bonds through the concrete, pictorial, and abstract approach. Here’s just one way to introduce and teach number bonds.

1. THE CONCRETE STEP
Children start out by counting familiar real-world objects that they can interact with. They then use counters to represent the real-world objects. From here, they progress to grouping counters into two groups.
By putting five counters into two groups, children learn the different ways that five can be made. For example, 3 and 2 as illustrated below. With further exploration, children work out other ways to break numbers into two groups.

2. THE PICTORIAL STEP

Now that they understand the concept with hands-on objects and experience, children progress to writing number bonds in workbooks or on whiteboards. Early number bond explorations might simply reflect the two groups of counters that they created during the concrete step, along with other combinations.

3. THE ABSTRACT STEP
With the concrete and pictorial steps done and dusted, children progress to representing abstract problems using mathematical notation (for example, 3 + 2 = 5).

TAKING THE CONCEPT A STEP FURTHER

Number bonds also develop problem solving strategies such as ‘making ten’ with ten frames, multilink or unifix cubes.

By mastering number bonds early on, pupils build the foundations needed for subsequent learning and are better equipped to develop mental strategies and mathematical fluency. By building a strong number sense, pupils can decide what action to take when trying to solve problems in their head.

This example shows how a pupil would develop their number sense, or mathematical fluency, by using number bonds to perform a mental calculation.

Ten is the most important number in our decimal (base ten) number system, so it is vital that our children learn to recognize it in any disguise. When the student knows the bonds for ten automatically, we move on to 20, then 100, then any number we desire.

SimpleSchool QUIZ WHIZ
QUIZ MAKER FOR ALL SUBJECTS

“My Number Bond App by Prime Academy Tech will gracefully enhance students’ integration into the concept of Number Bond, the Singaporean way” – Check it up!

PreSchool Grade book Maker

 

Don’t “Drill.” Play Games!

Here are a few number bond games to enjoy with your children:

  • Throw two dice and tell how many more you would need to make 10. (On the rare throws of 11 or 12, the answer is a negative number.)
  • Throw 3 dice and tell how many more it takes to make 20.
  • One player names any number 0-100, and the other tells how many more it takes to make 100.
  • You could also play the last game with math cards[take out the jokers and face cards, leaving just ace (1) through 10], turning up one for the tens place and one for the ones, to make a two digit number.
  • 10’s Concentration — Turn all the math cards face down on the table. On her turn, each player turns up two cards. If they add up to 10, she gets to keep them and try again. If one of the cards is a 10, she gets to keep it and turn up another card. Whoever takes the most cards, wins.
  • Use Number Bond App! –Check My Number Bond App!

-Credit: www.mathsnoproblem.com

Need a great PC app to help children learn Number Bond in a fun and interesting way? – Get My Number Bond App!

 

Excel Formula Expert 2

Excel Formulas in View

If you’re new to Excel, you’ll soon find that it’s more than just a grid in which you enter numbers in columns or rows. Sure, you can use Excel to find totals for a column or row of numbers, but you can also calculate a mortgage payment, solve math or engineering problems, or find a best case scenario based on variable numbers that you plug in.
 
Excel does this by using formulas in cells. A formula performs calculations or other actions on the data in your worksheet. A formula always starts with an equal sign (=), which can be followed by numbers, math operators (like a + or – sign for addition or subtraction), and built-in Excel functions, which can really expand the power of a formula.
 
For example, the following formula multiplies 2 by 3 and then adds 5 to that result to come up with the answer, 11.
 

=2*3+5

 
Here are some additional examples of formulas that you can enter in a worksheet.
 
  • =A1+A2+A3 Adds the values in cells A1, A2, and A3.
  • =SUM(A1:A10) Uses the SUM function to return sum of the values in A1 through A10.
  • =TODAY() Returns the current date.
  • =UPPER(“hello”) Converts the text “hello” to “HELLO” by using the UPPER function.
  • =IF(A1>0) Uses the IF function to test the cell A1 to determine if it contains a value greater than 0.

The parts of an Excel formula

A formula can also contain any or all of the following: functions, references, operators, and constants.
 

Parts of a formula

 
 
1. Functions: The PI() function returns the value of pi: 3.142…
 
2. References: A2 returns the value in cell A2.
 
3. Constants: Numbers or text values entered directly into a formula, such as 2.
 
4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

Using constants in Excel formulas

A constant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text “Quarterly Earnings” are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula. In general, it’s best to place constants in individual cells where they can be easily changed if needed, then reference those cells in formulas.
 

Using calculation operators in Excel formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. Excel follows general mathematical rules for calculations, which is Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction, or the acronym PEMDAS (Please Excuse My Dear Aunt Sally). Using parentheses allows you to change that calculation order.
 
Types of operators. There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

 

Arithmetic operators

To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results, use the following arithmetic operators.

 

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

 

Text concatenation operator

Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.

 
 
 

Reference operators

Combine ranges of cells for calculations with the following operators.
 
 

The order in which Excel performs operations in formulas

In some cases, the order in which a calculation is performed can affect the return value of the formula, so it’s important to understand how the order is determined and how you can change the order to obtain the results you want.
 

Calculation order

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). Excel interprets the characters that follow the equal sign as a formula. Following the equal sign are the elements to be calculated (the operands), such as constants or cell references. These are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
 

Operator precedence in Excel formulas

If you combine several operators in a single formula, Excel performs the operations in the order shown below. If a formula contains operators with the same precedence—for example, if a formula contains both a multiplication and division operator—Excel evaluates the operators from left to right.
 

Using parentheses in Excel formulas

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel performs multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the following example, the parentheses that enclose the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)

Using functions and nested functions in Excel formulas

 
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. You can find all of Excel’s functions on the Formulas tab on the Ribbon:
 

Excel function syntax

The following example of the ROUND function rounding off a number in cell A10 illustrates a function’s syntax.
1. Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
 
2. Function name. For a list of available functions, click a cell and press SHIFT+F3, which will launch the Insert Function dialog.
 
3. Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
 
4. Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips appear only for built-in functions.
 
NOTE: You don’t need to type functions in all caps, like =ROUND, as Excel will automatically capitalize the function name for you once you press enter. If you misspell a function name, like =SUME(A1:A10) instead of =SUM(A1:A10), then Excel will return a #NAME? error.
 

Entering Excel functions

When you create a formula that contains a function, you can use the Insert Function dialog box to help you enter worksheet functions. Once you select a function from the Insert Function dialog Excel will launch a function wizard, which displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.
 
To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula AutoComplete. After you type an = (equal sign) and beginning letters of a function, Excel displays a dynamic drop-down list of valid functions, arguments, and names that match those letters. You can then select one from the drop-down list and Excel will enter it for you.
 

Nesting Excel functions

In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
 
1. The AVERAGE and SUM functions are nested within the IF function.
Valid returns When a nested function is used as an argument, the nested function must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, the nested function must return a TRUE or FALSE value. If the function doesn’t, Excel displays a #VALUE! error value.
 
Nesting level limits A formula can contain up to seven levels of nested functions. When one function (we’ll call this Function B) is used as an argument in another function (we’ll call this Function A), Function B acts as a second-level function. For example, the AVERAGE function and the SUM function are both second-level functions if they are used as arguments of the IF function. A function nested within the nested AVERAGE function is then a third-level function, and so on.
 

Using References in Excel Formulas

 
A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula. You can use references to use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links or external references.

The A1 reference style

By default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through 1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.
 

Making a reference to a cell or a range of cells on another worksheet in the same workbook

In the following example, the AVERAGE function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.
 
1. Refers to the worksheet named Marketing
2. Refers to the range of cells from B1 to B10
3. The exclamation point (!) Separates the worksheet reference from the cell range reference
 
NOTE: If the referenced worksheet has spaces or numbers in it, then you need to add apostrophes (‘) before and after the worksheet name, like =’123′!A1 or =’January Revenue’!A1.
 

The difference between Absolute, Relative and Mixed References

Relative references:

A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
 
Copied formula with relative reference
 
 

 

Absolute references:

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.
 
Copied formula with absolute reference 
 

Mixed references:

A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
 
Copied formula with mixed reference
 
 

The 3-D Reference Style

Conveniently referencing multiple worksheets: If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference.
For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.
 
  • You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA, and VARPA.
  • 3-D references cannot be used in array formulas.
  • 3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection.
What occurs when you move, copy, insert, or delete worksheets: The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.
  • Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Excel includes all values in cells A2 through A5 from the added sheets in the calculations.
  • Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation.
  • Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.
  • Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them.
  • Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them.
 

The R1C1 Reference Style

 
You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an “R” followed by a row number and a “C” followed by a column number.
 
When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command, such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.
 
You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box. To display this dialog box, click the File tab.
 

Using names in Excel formulas

You can create defined names to represent cells, ranges of cells, formulas, constants, or Excel tables. A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how using them in formulas can improve clarity and make formulas easier to understand.
Example 1
 
Example 2
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
 
NOTE: In the formulas in columns C and D, the defined name “Sales” is substituted for the reference to (range) A9:A13 and the name “SalesInfo” is substituted for the range A9:B13. If you don’t create these names in your test workbook, then the formulas in D2:D3 will return the #NAME? error.
 
 

Types of names

There are several types of names that you can create and use.
 
Defined name: A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name. Also, Excel sometimes creates a defined name for you, such as when you set a print area.
 
Table name: A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of “Table1”, “Table2”, and so on, each time you insert an Excel table, but you can change these names to make them more meaningful.
 

Creating and entering names

You create a name by using the:
  • Name box on the formula bar: This is best used for creating a workbook level name for a selected range.
  • Create a name from selection: You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
  • New Name dialog box: This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.
NOTE: By default, names use absolute cell references.
 
You can enter a name by:
  • Typing: Typing the name, for example, as an argument to a formula.
  • Using Formula AutoComplete: Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
  • Selecting from the Use in Formula command: Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formula tab.
 

Using Array Formulas and Array Constants in Excel

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. Some of the built-in functions are array formulas, and must be entered as arrays to get the correct results.
 
Array constants can be used in place of references when you don’t want to enter each constant value in a separate cell on the worksheet.
 
Using an array formula to calculate single and multiple results
 
NOTE: When you enter an array formula, Excel automatically inserts the formula between { } (braces). If you try to enter the braces yourself, Excel will display your formula as text.
  • Array formula that produces a single result: This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.
For example, the following calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.
 
When you enter the formula ={SUM(B2:D2*B3:D3)} as an array formula, it multiples the Shares and Price for each stock, and then adds the results of those calculations together.
 
  • Array formula that produces multiple results: Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments.
For example, given a series of three sales figures (in column B) for a series of three months (in column A), the TREND function determines the straight-line values for the sales figures. To display all the results of the formula, it is entered into three cells in column C (C1:C3).
 
 
When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962), based on the three sales figures and the three months.
 

Using Array Constants

In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also called a constant. Similarly, in an array formula you can enter a reference to an array, or enter the array of values contained within the cells, also called an array constant. Array formulas accept constants in the same way that non-array formulas do, but you must enter the array constants in a certain format.
 
Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A. Different types of values can be in the same array constant — for example, {1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format. Text must be enclosed in double quotation marks — for example, “Tuesday”.
 
Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent sign).
 
When you format array constants, make sure you:
  • Enclose them in braces ( { } ).
  • Separate values in different columns by using commas (,). For example, to represent the values 10, 20, 30, and 40, you enter {10,20,30,40}. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference.
  • Separate values in different rows by using semicolons (;). For example, to represent the values 10, 20, 30, and 40 in one row and 50, 60, 70, and 80 in the row immediately below, you enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.
 

Delete a Formula

When you delete a formula, the resulting values of the formula is also deleted. However, you can remove just the formula and leave the resulting value of the formula displayed in the cell.
To delete formulas along with their resulting values, do the following:
  • Select the cell or range of cells that contains the formula.
  • Press DELETE.
To delete formulas without removing their resulting values, do the following:
  • Select the cell or range of cells that contains the formula.
If the formula is an array formula, select the range of cells that contains the array formula.
 
How to select a range of cells that contains the array formula
  1. Click a cell in the array formula.
  2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.
  3. Click Special.
  4. Click Current array.
  • On the Home tab, in the Clipboard group, click Copy .
 
Keyboard shortcut; You can also press CTRL+C.
  • On the Home tab, in the Clipboard group, click the arrow below Paste , and then click Paste Values.

Avoid Common Errors when Creating Formulas

The following table summarizes some of the most common mistakes you can make when entering a formula and how to avoid formula errors:
 

Excel Formula Expert 1

 
 
 

What is Excel Formula ? 

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things that can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=). 
 
This Article is written for the people who does not know about Excel formula. This article answers following questions about Excel Formulas:
 
 
1. What is Excel Formula?
2. How to Use Excel Formula – a step by step tutorial.
3. How an Excel Formula works in Excel?
4. How to refer a Cell in Excel Formula?
5. What is Cell Address?
 

How to Use Excel Formula – Step by Step 

1. Select the Cell where you want to place your formula. 
2. Type the Equal (=) sign. 
 
Note: After typing = sign if you Click on Any other Cell then the Address of that Cell will be automatically placed there. So be careful while writing a formula in Excel. While writing formula DO NOT select any other cell, unless you want to refer that Cell in to your formula. Therefore you can refer a cell in to your formula in two ways: 
 
i) By typing the Address of that Cell (like A1, B8 etc) 
 
ii) By Selecting that Cell while writing your formula. 
 
3. Type the address of the cell to include in the formula or Select that Cell. 
 
4. Type the Formula as per the Syntax of that Formula 
 
5. Press Enter or Press Tab Key. (Again after writing the Complete formula, do not click somewhere else, instead you need to press Enter or Tab key Only)
6. And now you are done. 
 
 
Let’s take an example of Simple Formula in Excel. 
 
In Cell A1, I have value as 10 and in Cell B1, I have value 5. I want sum of these two Cell values in Cell C1. 
 
1. Click on Cell C1
2. Type Equal to Sign (=)
3. Now Take your mouse at Cell A1. Automatically the Cell Address A1 will be typed in Cell C1.
4. Now Type Plus Sign ( + )
5. Now Click on the Cell B1. (Same as previously this time B1 Cell Address will be typed)
6. Now It will look as same as shown in below: 
 
7. Now press Enter
8. Value of Cell A1 (i.e. 10) and Value of Cell B1 (i.e. 5) will be added and the Result will be shown in Cell C1 (Where formula is written). 
 
 
Note: In the above Example, you can see that the Value of Cell is referred by the Address of that Cell. It means in Excel Formula if you want to Refer the Value of Cell then you can refer it by passing the Address of that Cell.
Address of Cell is formed by Column Name and Row Number. It means if you want a Cell which is in Column J and Row 3 then the Address of the Cell will be J3.

The Power of Excel Formulas

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
 
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.
 

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

To create a formula:

In our example below, we’ll use a simple formula and cell references to calculate a budget.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell B3.

 

2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.

3. Type the cell address of the cell you want to reference first in the formula: cell B1 in our example. A blue border will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
 

5. Type the cell address of the cell you want to reference second in the formula: cell B2 in our example. A red border will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. 

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell D3.

2. Type the equals sign (=).

3. Select the cell you want to reference first in the formula: cell B3 in our example. The cell address will appear in the formula, and a dashed blue line will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the multiplication sign (*).
 
5. Select the cell you want to reference second in the formula: cell C3 in our example. The cell address will appear in the formula, and a dashed red line will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. 
 
 

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.
 

1. Select the cell containing the formula you want to edit. In our example, we’ll select cell B3.

2. Click the formula bar to edit the formula. You can also double
-click the cell to view and edit the formula directly within the cell.

3. A border will appear around any referenced cells. In our example, we’ll change the second part of the formula to reference cell B2 instead of cell C2.

4. When you’re done, press Enter on your keyboard or click the checkmark in the formula bar.
 
 
5. The formula will be updated, and the new value will be displayed in the cell.
 
 
If you change your mind, you can press the Esc key on your keyboard to avoid accidentally making changes to your formula.
 
To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the top-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.

Working With Excel In The Cloud- An Intro To Onedrive

 
 

EXCEL AND ONE DRIVE

 
 
You might have heard the phrase “working in the cloud” and wonder what that means, and how that affects the way you work with Excel. It’s really all about providing easy access to your data via a browser—to anyone, anywhere, at anytime. No need for other people to have Excel installed to view, edit, or collaborate on that data.
 
By using Excel Online, a browser-based extension of Excel, you will be able to do all that. You can even use it to create a new workbook on a computer that doesn’t have Excel installed. 
 
You can view and edit workbooks in a browser in one of two ways: 
 
· By uploading or creating new workbooks on OneDrive, which is a free Web-based file storage and sharing service. 
 
· By saving workbooks to a SharePoint site where Office Online is installed. 
 

What is Excel Online? 

Excel Online is a browser-based application that you can use to view and edit Excel workbooks in your browser. Workbooks and worksheets look the same in Excel Online as they do in Excel, and Excel Online has the familiar look and feel of Excel. Many Excel features are available in Excel Online, but not all features are supported or work exactly the same way. To make changes beyond what you can do in the browser, Excel Online includes a way for you to open the workbook in Excel, and then save it back to the server. 

Excel Online makes it easy for you to: 
 
· Extend Excel on the Web Use Excel features and functionality you are familiar with, in a Web environment. 
 
· Work anywhere A browser is all you need to access your workbooks. 
 
· Work together Your teammates can work with you on projects regardless of which version of Excel they have. 
Using OneDrive to access Excel Online 
 
To use Excel Online on OneDrive, you need a Microsoft account. Use that ID to sign in and then go to OneDrive to access Excel Online. To work on existing Excel workbooks in Excel Online, you can upload those workbooks in OneDrive. 
Create a OneDrive ID 
 
1. Go to http://OneDrive.live.com. 
 
2. Click Sign up. 
Upload an existing workbook 
 
To upload one or more workbooks for use in Excel Online, do the following: 
 
1. Sign into OneDrive. 
 
2. Under Files, click the folder where you want to upload the workbooks. 
 
Folders that display a lock icon are private folders; folders can also be shared folders or public (internet) folders. 
 
NOTE: By clicking Create and then Folder, you can create a new folder where you can upload your workbooks. 
 
3. Click Upload to add the workbooks you want to open in Excel Online. 
Start a new workbook in Excel Online 
 
Instead of uploading a workbook from your computer, you can add a new, blank workbook to a OneDrive folder directly in the browser. 
 
1. Sign in to OneDrive. 
 
2. Click the folder where you want to add a new workbook. 
 
3. Click Create, and then click Excel workbook. 
 
Excel Online opens in edit mode. 
 
NOTE: There’s no need to save your changes. Excel Online saves your workbook automatically while you work on it. If you make changes you don’t want to keep, use the Undo command (or press CTRL+Z). 
View worksheet data in Excel Online 
 
When you open a workbook in view mode in Excel Online, you can view and interact with the worksheet data to help you interpret it. For example, you can sort and filter the data in columns or expand PivotTables to see additional details and data trends. To make changes to the data, you must open the workbook in edit mode. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to view. 
 
3. Click the workbook that you want to explore. 
Edit worksheet data in Excel Online 
 
When you work on an Excel Online workbook in edit mode, you can change the data, enter or edit formulas, and apply basic formatting to draw attention to the data. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to edit. 
 
3. Click the workbook you want to change. 
 
Excel Online opens in edit mode. 
 
To make advanced editing changes to the worksheet that exceed the capabilities of Excel Online, do the following: 
 
1. Click Open in Excel. 
 
2. In Excel, make the editing changes you want. 
 
3. On the File tab, click Save. 
 
The workbook is automatically saved in the OneDrive folder. 
 
Use Excel Online to collaborate on worksheet data with other people 
 
To collaborate with other people on the same workbook at the same time, you can share a workbook with them. This feature is useful when you have a worksheet that requires input, such as a group project or a signup sheet. No more sending a workbook around via e-mail, or waiting for someone else to check it back in on the server. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to share. 
 
NOTE: Other people will be able to access everything in the folder you select because you cannot provide permission to individual workbooks only. To keep specific workbooks private, use one or more folders for private workbooks, and create a separate folder for the workbooks you want to share. 
 
3. Click Share. 
 
4. Invite the people you want to share the file with. 
 
5. Click Share. 
 
6. Back in the folder, click the workbook. 
 
After users click the link you sent them and start working on the worksheet in edit mode, you can see the changes that they make. 
 
NOTE: If changes are made to the same cell by different people (including you), the last change will displayed in the cell. 

Collaboration Becomes Easy

Many of the features in Office are geared toward saving and sharing files online. OneDrive is Microsoft’s online storage space you can use to save, edit, and share your documents and other files. You can access OneDrive from your computer, smartphone, or any of the devices you use.
Once you have a microsoft account, you will be able to sign in to Office. Just click Sign in in the upper right corner of the excel window.
 

Benefits of using OneDrive

Once you’re signed in to your Microsoft account, here are a few of the things you’ll be able to do with OneDrive:
 
· Access your files anywhere: When you save your files to OneDrive, you’ll be able to access them from any computer, tablet, or smartphone that has an Internet connection. You’ll also be able to create new documents from OneDrive.
 
· Back up your files: Saving files to OneDrive gives them an extra layer of protection. Even if something happens to your computer, OneDrive will keep your files safe and accessible.
 
· Share files: It’s easy to share your OneDrive files with friends and coworkers. You can choose whether they can edit or simply read files. This option is great for collaboration because multiple people can edit a document at the same time (this is also known as co-authoring).

Saving and Opening Files

When you’re signed in to your Microsoft account, OneDrive will appear as an option whenever you save or open a file. You still have the option of saving files to your computer. However, saving files to your OneDrive allows you to access them from any other computer, and it also allows you to share files with friends and coworkers.
 
For example, when you click Save As, you can select either OneDrive or This PC as the save location.

Why Excel?

Welcome To The World Of Analysis With Excel…

Microsoft Excel was released in 1985 and has grown to become arguably the most important computer program in workplaces around the world. Whether you are budgeting, organizing client sales lists, or need to plan an office social gathering, Excel is a powerful tool that has become entrenched in business processes worldwide. 

Why Excel?

Among the computer programs which exist, Microsoft Excel is one of the most important because of the key role it plays in many sectors. It is the most used spreadsheet program in many business activities, classwork and even personal data organisation. Since 1985, it has played a vital role in performing formula based arithmetic and calculations, and other activities that may require mathematical calculations. Many businesses, personal and institutional enterprises have embraced the use of Excel because of its utility and the ability to serve as a visual basic for different applications. 

Brief History of MS Excel 

 Some Areas of Excel Functionalities

The importance of MS Excel can be seen in the different departmental units it is used as follows:

 

Finance and Accounting 

 
If you walk through the finance or accounting department at any major corporate office, you will see computer screens filled with Excel spreadsheets outlining financial results, budgets, forecasts, and plans used to make big business decisions. 
This is the area of business with the biggest reliance and benefit from Excel spreadsheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today. 
 
Most users know that Excel can add, subtract, multiply, and divide, but it can do much more with advanced IF functions when coupled with VLOOKUP, INDEX-MATCH-MATCH, and pivot tables.

Marketing and Product Management

While marketing and product professionals look to their finance teams to do the heavy lifting for financial analysis, using spreadsheets to list customer and sales targets can help you manage your sales force and plan future marketing plans based on past results. 
 
Using a pivot table, users can quickly and easily summarize customer and sales data by category with a quick drag-and-drop. All parts of business can benefit from strong Excel knowledge, and marketing functions are not exempt.
 

Human Resources Planning 

 
While database systems like Oracle (ORCL), SAP (SAP), and Quickbooks (INTU) can be used to manage payroll and employee information, exporting that data into Excel allows users to discover trends, summarize expenses and hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level. 
 
HR professionals can use Excel to take a giant spreadsheet full of employee data and understand exactly where the costs are coming from and how to best plan and control them for the future. 

Education

 
A good use of Excel in schools and educational institutions can automate classroom activities, like games, quizzes and tests. More importantly, it can be used for computation and analysis of test scores, logs, attendance and so on.
 
For example, Excel based App like SimpleSchool Grading and Analysis App can compute, analyse and generate instant report sheets for students by just inputting the students scores.
 

Data Organization

Even non-financial businesses employ Excel spreadsheets. The ability to organize data in Excel tables and set up tools for updating, organizing, and displaying the data makes the program popular for administrative purposes.
 
Comprehensive, easy-to-reference Excel tables allow administrators to check a single statistic in a process, such as service fault issues in a product, against a report of trends for the same service faults. Through these comparisons, issues can be identified and solutions developed.

Programming

 
Excel supports Microsoft’s Visual Basic for Applications programming language for creating macros that increase efficiency and ease of use by automating or simplifying complex functions.
 
People who are proficient at creating Excel macros are much in demand at organizations of all types.

Graphing

 
Excel also produces a variety of charts and graphs for representing income statements and other statistical data visually. You save time by making the charts in the spreadsheet where the data resides.
 
Visual presentation of the data drives home your points more effectively than columns of numbers. Being able to graph within Excel also frees up some capital that otherwise would have to be invested in another graphing program.

You Can Do Anything With a Spreadsheet

 
Using Excel for business has almost no limits for applications. Here are some examples:
When planning a team outing to a baseball game, you can use Excel to track the RSVP list and costs.
Excel creates revenue growth models for new products based on new customer forecasts. 
 
When planning an editorial calendar for a website, you can list out dates and topics in a spreadsheet.
When creating a budget for a small product, you can list expense categories in a spreadsheet, update it monthly and create a chart to show how close the product is to budget across each category.
You can calculate customer discounts based on monthly purchase volume by product.
Users can summarize customer revenue by product to find areas where to build a stronger customer relationships.
 
This is a very short list to give you an idea of the diverse uses for Excel.

The Bottom Line

Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics.
 
A working knowledge of Excel is vital for most office based professionals today, and stronger Excel skills can open the door to promotion and leadership opportunities. Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.