basic method totals values within the range

    =SUM(D38:D41)
    

totals the values of the cell, the range, and the value

    =SUM(D48, G48:G51, 100)
    

total the values of all cells where cell value is greater than 50

    =SUMIF(D73:D77,">50")
    

to copy a cell's value to a column of a row click the low right corner of the highlighted drag the cell dow the column or across the row

worksheet with column headers email, First name, and Last name
only email column filled out in format

    <first name>.<lastname>@xyz.com
    

in the first data row enter the first and last names into their respective columns

go down the first name column entering the first name
when grayed out intellisense appears hitting enter will automatically fill the column

click on cell C2
use ribbon to navigate to Home > Fill > Flash Fill

select first few cells in column B
on Data tab click 'Text to Columns'
select Delimited and click next
under Delimiters only Comma should be selected
click next
click the general option
click destinationnbox and enter $E$2
click Finish

cell A9 contains 'A B C'
in cell B9 use the formula below

    =LEFT(A9,FIND(" ",A9)-1)
    

cell B9 now contains the letter A
in cell C9 use the formula below

    =RIGHT(A9,LEN(A9)-FIND(" ",A9))
    

cell C9 now contains "B C"
in cell D9 use the LEFT formula but the target cell is C9

    =LEFT(C9,FIND(" ",C9)-1)
    

cell D9 contains the letter B
in cell D10 use the RIGHT formula but the target cell is C9

    =RIGHT(C9,LEN(C9)-FIND(" ",C9))
    

the FIND function returns number representing a position in a string
only difference is where the extraction starts

    =FIND(<character to find>,<targetCell>);
    

the LEFT and RIGHT functions return a substring from the target cell

    =LEFT(<targetCell>,<number of characters to return>)
 
    =RIGHT(<targetCell>,<number of characters to return>)
    

to switch columns to rows or rows to columns

1. click and drag columns/rows to be transposed
2. copy the selection using Ctrl+C
3. click the cell where the transposed data will start
4. on Home tab click the arrow under the paste button
5. click Paste Special
6. in dialog click Transpose and the Enter

transpose using an array. formula

1. select blank cells where the data will be transposed to
2. with celss selected enter the following command but do not hit Enter

       =TRANSPOSE(<range of cells to transpose from>)
       

3. press CTRL+SHIFT+ENTER

to sort a column click a cell in the column and click Home tab's Sort & Filter button and select a sort
if the sheet has headers cliccustom sort and check the 'My data has headers' check box
right-click a column header and Sort from the context menu

tables provide features and conveniences
click cell in data to become the table
to create table click Insert > Table, > OK
table grows as rows are added
can add columns by dragging table from lower right corner

1. click the cell in the row which has the columns to be added
2. press ALT+Equals
3. press Enter
4. the SUM formula gets filled by Excel

1. click a cell in the table
2. the Design tab opens
3. click total rows
4. a row will be added with the totals
5. click the cell next to a total and click the down arrow
6. can select different formula to get the cell's value

insert a dropdown list

1. open Data > Data Validation to open the Data Validation dialog
2. change Allow to List
3. click the up arrow new to the source textbox
4. select the cells in a column which will have the data for the dropdown
5. click down arrow and the OK

best practice is to use a table as the source
rows added to the table will appear in the dropdowns

select cells to be analyzed
click button at lower right corner of selection
use resulting dialog
quickly make a chart

1. click a cell and press ALT + Q
2. click Charts > Clustered
3. drag chart to desired location

click on data then click Insert > Recommended Charts

create a Pivot Table

1. cell a cell with the data to be used
2. on the Insert tab click Pivot Table
3. PivotTable Fields pane will be display on right of IDE
4. select the fields to be used in the pivot table