Excel tips and tricks

  • Published on
    15-Apr-2017

  • View
    189

  • Download
    0

Transcript

Excel Tips & TricksPrepared By:-Ravi Sapariya

1

Save you lots of timeMove/Select the first or last cell of a contiguous data block without scrolling

Ctrl + Up/Down - Moves to the top or bottom cell of the current columnCtrl + Left/Right - Moves to the cell furthest left or right in the current rowCtrl + Shift + Up/Down/Left/Right - Selects all the cells above or below the current cellShift + Space to Select current columnCtrl + Space to Select current row

Why you need to know this

How to use this featureSome quick useful tips

2

Split and Freeze Function

Why you need to know thisSplitting a window allows you to work on multiple parts of a large spreadsheet simultaneously

Freezing the pane allows you to always keep one part of the spreadsheet (e.g., column or row labels) visible

How you use this featureDrag the split horizontal and split vertical icons to the desires positions

Click on the freeze pane icon from the tool bar to freeze the panes

3

Min & Max Function

Why you need to know thisMAX and MIN functions will simply return the largest and smallest result from a range of numbers. Lets use our Test score example from above one more time.

How you use this featureSelect the Range of cell and apply the function

Eg. =MAX(B6:B31) for maximum = MIN(B6:B31) for minimum

4

Conditional Function-IF & SUMIF

Why you need to know thisThe IF function is used to determine whether a statement is True or False and then performs an action based on the result. The IF statement is broken out as

How you use this featureIF(Criteria,True value,False value)

Lets Take an example

5

Conditional Function- AND & OR

Why you need to know thisThe AND function is a logical function that checks multiple criteria and will return a TRUE value if ALL of the criteria are TRUE. Otherwise it returns a false.

The OR function works similar to the AND statement. It checks multiple criteria however it only requires ONE statement to be true to make the whole statement TRUE.

Eg.

How you use this feature

6

COUNTIF

Why you need to know thisThe COUNTIF function works the same way as the SUMIF, however it just counts the fields that match a certain criteria, instead of summing them. See the following example.

Eg.

How you use this feature

7

PIVOT TABLES

Why you need to know thisPivotTables are essentially summary tables that let you count, average, sum, and perform other calculations according to the reference points you enter. They can be used to summarize, analyze, explore and present your data.

Select range of cells and Insert Pivot Table

Lets take and easy and fast example

Reference url : http://letmeknw.in/pivot/

How you use this feature

8

PIVOT TABLES

Why you need to know thisPivotTables are essentially summary tables that let you count, average, sum, and perform other calculations according to the reference points you enter. They can be used to summarize, analyze, explore and present your data.

Select range of cells and Insert Pivot Table

Lets take and easy and fast example

Reference url : http://letmeknw.in/pivot/

How you use this feature

9

Charts

Why you need to know thisExcel charts help you communicate insights & information with ease. By choosing your charts wisely and formatting them cleanly, you can convey a lot

Select Chart Type and Range of Cell

Lets take and easy and fast example. Simple charts, combination of charts

Reference url : http://letmeknw.in/charts/

How you use this feature

10

Transpose

Why you need to know thisIf data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other.

Select Range Where the data will be pasted

Select Range which is to be transposed and press

Ctrl + Shift + Enter

Eg. Lets Take and example

How you use this feature

11

Suppress Error Cells

Why you need to know thisBecause of some inconsistence data you may see Errors in you excel sheet.For example is something is divided by zero

That can be suppressed using following tip

Eg. Lets Take and example

How you use this feature

12

MACROIf you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks.

A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes.

Why you need to know this

How you use this featureExcel-> Excel Optins->Show Developer Tab

Select any cell, Select Record Macro->Ok

Start your work then press Stop Recording

To Run Macro-> Enter Short Cut Created or Macro->Select the Macro to Run

Lets take an example