Excel

Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses.

- =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. - Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.

Center Align Data

- COMMAND+E

Delete Everything in Selected Cells

- FN+DELETE

merge cells using the Merge & Center command

- Merge & Center: merges the selected cells into one cell and centers the text - Merge Across: merges the selected cells into larger cells while keeping each row separate - Merge Cells: merges the selected cells into one cell but does not center the text - Unmerge Cells If you merge multiple cells that all contain data, Excel will keep only the contents of the upper-left cell and discard everything else.

Increase/Decrease Font Size

- SHIFT+COMMAND+. (increase) SHIFT+COMMAND+, (decrease)

Create Filter

- SHIFT+COMMAND+F

To sort a sheet

- Select a cell in the column you want to sort by - Data tab on the Ribbon, then click the A-Z command to sort A to Z, or the Z-A command to sort Z to A

To sort a range

- Select the cell range you want to sort - Data tab on the Ribbon, then click the Sort command - The Sort dialog box will appear. Choose the column you want to sort by. - Decide the sorting order (either ascending or descending). In our example, we'll use Largest to Smallest - double-check your cell values to make sure they are entered into the worksheet correctly

To create a custom sort

- Select the column you want to sort by, then choose Custom List... - Select NEW LIST from the Custom Lists: box - Type the items in the desired custom order in the List entries In 2011 Type a custom list from scratch, making entries in Excel Preferences.

center across selection

- Select the desired cell range - Click the small arrow in the lower-right corner of the Alignment group on the Home tab - Locate and select the Horizontal drop-down menu, select Center Across Selection, then click OK - The content will be centered across the selected cell range. As you can see, this creates the same visual result as merging and centering, but it preserves each cell within A1:F1 Mac 2011: format cells (command 1) - alignment - horizontal - center across selection

Freeze rows (to see certain rows or columns all the time in your worksheet, especially header cells)

- Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we'll select row 3 - view - freeze pane 2016 In 2011, layout - windows - freeze pane - If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Column from the drop-down menu

insert rows

- Select the row heading below where you want the new row to appear. - Click the Insert command on the Home tab

applying number formats

- for 5 percent type 5% or 0.05 and you can change it into percentage terms by choosing from the drop down menu - command 1

move a column

- if you want to move a column between columns E and F, select column F - Click the Insert command on the Home tab, then select Insert Cut Cells from the drop-down menu.

Formatting attributes

Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%. You can even change a cell's text or background color.

Formulas and functions

Cells can contain formulas and functions that calculate cell values. In our example, SUM(B2:B8) adds the value of each cell in the cell range B2:B8 and displays the total in cell B9.

select cell style

Click the Cell Styles command on the Home tab, then choose the desired style from the drop-down menu

To wrap text

Click the Wrap Text command on the Home tab

The column width changed automatically to fit the content

Double-click the mouse

Cell Range

E.g. A1:F8

pound signs

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.

Sorting levels

If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column. - Select a cell in the column you want to sort by - Data tab, then select the Sort command - The Sort dialog box will appear. Select the first column you want to sort by. - Click Add Level to add another column to sort by. - The worksheet will be sorted according to the selected order. In our example, the orders are sorted by T-shirt size. Within each group of T-shirt sizes, students are sorted by homeroom number. - If you need to change the order of a multilevel sort, Simply select the desired column, then click the Move Up or Move Down arrow to adjust its priority.

Compatibility Mode

In Excel 2016, file-backstage view-convert. OR file-options-convert. OR to the right of the function button-compatibility check in mac 2011

Pin a work book

In Excel 2016, you can pin a workbook in the backstage view. (The pushpin button to the very right of the file name)

New window

Layout - new window - created for the current workbook

To include Print Titles

Page layout - repeat titles

To check spellings

Review - spelling - change fn F7

To add a border

Right click mouse - Format cells - borders

Autosum a Bunch of Numbers - MacBook

SHIFT+COMMAND+T

delete or clear cell content

Select cells - clear command on home tab - clear contents - we're left with blank cells OR use the Delete key on your keyboard to delete content from multiple cells at once. The Backspace key will only delete content from one cell at a time.

Delete cells

Select the Delete command from the Home tab on the Ribbon.The cells below will shift up and fill in the gaps.

to use the fill handle

Select the cell(s) containing the content you want to use, then hover the mouse over the lower-right corner of the cell so the fill handle appears Copy and Paste: - Write the formula in first cell. - copy the formula by pressing Command+C - Select all the cells you want to fill it across by using shift+Down arrow key. - Now paste the copied formula by pressing Command+V. Ctrl+D: - Write the formula in first cell. - Press enter - Now again come to first cell where you applied the formula. - Select all the cells you wish to fill it across by using shift+Down arrow key. - Press Ctrl+D to fill the formula automatically.

hide or unhide a row or column

Select the columns you want to hide, right-click the mouse, then select Hide from the formatting menu To unhide the columns, select the columns on both sides of the hidden columns. In our example, we'll select columns B and F. Then right-click the mouse and select Unhide from the formatting menu

Using cell references with multiple worksheets

Sheet1!A1 'July Budget'!A1 - If you rename your worksheet at a later point, the cell reference will be updated automatically to reflect the new worksheet name - If you enter a worksheet name incorrectly, the #REF! error will appear in the cell

Selecting: command on mac vs shift

Shift: lets you select multiple cells using arrow keys Command: select the cell on top

AutoFit the width for several columns at the same time

Simply select the columns you want to AutoFit, then select the AutoFit Column Width command from the Format drop-down menu on the Home tab. This method can also be used for row height

a function must be written a specific way, which is called the syntax.

The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate.

COUNT

This function counts the number of cells with numerical data in the argument.

Insert something into the cell

Type something into the selected cell, then press Enter on your keyboard. The content will appear in the cell and the formula bar. You can also input and edit cell content in the formula bar.

Adjust page breaks

Vertical and horizontal blue dotted lines denote the page breaks. Click and drag one of these lines to adjust that page break

To split a worksheet

Window - split in 2011 View - split in 2016

A function e.g. AVERAGE(B1:B9)

a predefined formula that performs calculations using specific values in a particular order. sum min max..

Relative references

change when a formula is copied to another cell

Select a cell

click it. A border will appear around the selected cell, and the column heading and row heading will be highlighted. The cell will remain selected until you click another cell in the worksheet.

To unfreeze rows or columns

click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu

to group worksheets

command and choose the sheets you want to group and release command

copy

command c

Cut and paste cells

command x

control + and -

delete or add rows or columns

Increase and Decrease Decimal

don't work with some number formats, like Date and Fraction

To edit formula

double-click the cell or edit in the formula bar

Find and replace

find and select - replace - find what & replace with CONTROL + H or COMMAND + SHIFT + H

print selected area

first select the cells you want to print

To use the Insert Function command

formulas - insert functions - type in a few keywords

command h

hides the whole window of excel on MacBook

To show all of the formulas in a spreadsheet

hold the Ctrl key and press `. 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.

Sort sheet

organizes all of the data in your worksheet by one column. Related information across each row is kept together when the sort is applied

to avoid accidentally making changes to your formula

press the Esc key on your keyboard or click the Cancel command in the formula bar

Absolute references

remain constant no matter where they are copied $E$2

to drag and drop cells

select the cells you want to move - hover the mouse over the border until it changes into a pointer with 4 arrows or a hand icon on MacBook

the Function Library group

select the desired function category. In our example, we'll choose More Functions, then hover the mouse over Statistical - COUNTA

insert columns

select the row to the right of where you wanna add a new role

command enter

select whole row below

command enter command shift enter

selecting exact number of cells below or above already selected ones

Cells selected - return

shift to next cell for you to edit

Sort range

sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet.

SUM

sum icon OR formulas - sum OR alt =

Cell content

text (letters, number, dates), formatting(), formulas, and functions

Format painter

to copy formatting from one cell to another, you can use the Format Painter command on the Home tab. When you click the Format Painter, it will copy all of the formatting from the selected cell. You can then click and drag over any cells you want to paste the formatting to.

COUNTA function

to count the total number of items in the Items column. Unlike COUNT, COUNTA can be used to tally cells that contain data of any kind, not just numerical data.

delete a row or column

v.s. clearing its contents

date entry

you'll need to use a specific format your spreadsheet understands, such as month/day/year so that the spreadsheet will automatically make it a date or -March 15 instead of March 15th OR control ; for date, command ; for time - More Number Formats. These are options to display the date differently, like including the day of the week or omitting the year