Basics

Quick access toolbar – topmost- can modify by using down arrow there or Go to a function like sum sign sigma and right click and add.

Can use evaluate formula under formula button to check formula

Under the home tab,  in centre we can change format of a cell.

Date us as stored as a number in excel. Even if it displays differently.

Can even customize it to your preferred display.

Type  = function name and Click on fx near formula bar to get help on function arguments.

SUM,Min,Max,Average, Count.

Count – counts all numeric values in a range of cells.

Green tick on left top of a cell shows a potential error.. that is excel might think it is an error.

Can click on green and then yellow diamond sign and ignore error. Can select a range of cells and ignore error too.

Click on a cell and hit auto sum Sigma button to get sum of all cells left or top. Or Alt =

To shift a table of cells, highlight, Go to border, and left click and drag.

To remove the scissor border after hitting ctrl+x, use esc key

Select row and hit ctlr+ to insert row above.( + should be on number pad) or use ctrl shift+

Use ctrl- to delete highlighted rows/columns.

Got to column border and double click to autofit to the largest value.

For a set of columns to have uniform width, highlight all of them, and adjust anyone in the middle… all the highlighted columns will now have the adjusted width..same for rows… to autofit highlighted columns, just highlight, Go the border in anyone of the columns , and double click.

Negative currency values are sometimes given as () and are in red.

Collapse/Expand the top ribbon by pressing Ctrl F1 or hit the small arrow/pin on the ribbon at the top right.

Hiding

To hide a row/column, select a row/column and right click and hide. Can do or multiple columns also

Unhide –  Go there and left click and expand

Go to view tab and uncheck headings to not view row/column headings

Double click on sheet1 to rename.

Sheets

Can’t undo deleting a sheet… except not saving closing and reopening.

Left click a sheet and drag to reorder sheets.

To get a copy of a sheet into another, ctrl + left click and drag

To move/copy to different workbook, right click and do to any of the current open workbooks.

Excel can have 255 sheets

Formatting cells

To merge cells. Select cells and under Home>Alignment>Merge and Centre. Click again to unmerge.

To change background colour, select cells, and hit paint bucket.

To make borders for cells, select cells Go to home , under font, there is a border sign( + in a box)

Select cells , under home, change general to accounting to get rupee sign, to change back click comma sign.

Select percent to multiply by 100 and add % symbol.

Can also increase and decrease decimals on the 000 button below.

Copy only Formatting- select cell , Go to clipboard under home, click on paint brush, and then click on final cell to copy only the formatting.(copies font, background colour etc, but not data)

Styles

Go to home > styles> cell styles and create your own style.(can use in MS word(select text first and then right click in Word) , ppt also)

                1. do the above to create a style(select format to modify) .

                2. to apply  to other cells, just select them  and Go to cell styles and do the same.

                3. always use styles- If u want to remove /modify formatting, it is tedious to do for all cells. Jus modify style instead. Go to cell styles and right click to modify.

                4. use clear format-home>editing>eraser sign to clear al formatting for cell(s).

Conditional Formatting

Suppose you want to change font colour  to red if cell value > 100. Select cells, then Home>Styles>conditional formatting>highlight cell rule > greater than > enter 100. It is dynamic. Change values, and check. Can also use custom format.

Inserting Images and charts

Insert >Illustrations>Online pictures>Bing or onedrive> pics with Creative commons licence

Insert>Illustrations>Icons>

Can copy, paste these multiple places.

Insert>Illustrations>shapes> . can insert arrows to important numbers. Can add box and then right click format to no fill to highlight an area. Can change border thickness and color(right click outline>weight or color)

Shapes like smiley come with yellow markers.. can change to frown by clicking on yellow marker and dragging.

Select Table of cells along with heading and Go to insert>Chart>2dchart.

Click chart. + sign pops, and on can modify there. Can modify individual text in the chart.

To modify data source, click on empty area in the chart. This will highlight the data source. Go to the data source. Click  the border squares and drag to extend /reduce the columns.

Click on chart. Design and format tab op on top. Can select individual bars and change shape fil. Can change designs.

Can change background color – select chart and shape fill.

MOVE chart – Select chart>Design tab>Move>type new sheet name.

Can use pie chart. If problem, Go to design tab> Switch row/column.

Printing -Do later

Page layout view

To print headers in page 2 and so on, go to page layout>print titles>sheet> rows to repeat on top and select the headers row.

See View > page break view  to see page numbers(watermark)

Go to page layout>print titles>sheet> over then down for getting correct print output.(print all columns and then rows)

Templates

File>new> select template.

Or if you have a table with headings and formatting, select save as and then select .xlts format.- Template format.

Select new> personal template to view.Have headers with separate formatting so that excel doesn’t sort them too. – Excel doesn’t include it in the list.

Don’t have a deleted row in middle –  excel doesn’t sort after the deleted row.

Select a cell. Use Ctrl Shift  down arrow on keyboard to select only the list in a column below the selected cell.

After this you may use ctrl shift right arrow to select entire table

Can do multiple level sort- select cell . Then Data> Sort button> add level .

First level – sort on first name

Second level – sort on second name. If two first names are same , it sorts based on second level.

Excel can sort up to 64 levels

REFERENCES-:

Notes by Srikanth . S. from https://www.udemy.com/course/microsoft-excel-2013-from-beginner-to-advanced-and-beyond/learn/lecture/13655324?start=45#overview