NAVIGATION - SEARCH

Be an Excel Pro - Wonderful tips and How To Using Excel

MS Excel, no doubt is one of the best software available for number crunching professionals as well as for starters who have just started their flight to become professionals. It is a skill that every person wants to have in their resume. Whether you are a pro or a newbie, one thought is always common which is "not knowing enough". Here we have listed down various tips that will help you work more efficiently and avoid manual or tedious work.

Vlookup: this function is used to match or combine data between 2 sheets having 1 column as constant. For e.g. if you have email, name, age in one sheet and email, company, location in another sheet and you want to add company, location against the email, then Vlookup is the perfect function. The formula to do that is =vlookup(sheet1 email column, sheet2 select all email company location columns, value number to pull, false). To explain this further:

  • Select the cell where you want to pull company name
  • Start by typing =vlookup(
  • Select the cell (assuming A2) that contains email in Sheet1, now formula becomes =vlookup(A2,
  • Go to Sheet2 and select all columns and rows containing data (assuming A2 having email, B2 having company and C2 having location)
  • Now the formula becomes =vlookup(A2, Sheet2!A2:C100,
  • To get company value, type 2 since this is the second column in selection - =vlookup(A2, Sheet2!A2:C100, 2, false)
  • False is used to ensure exact match is returned
  • Repeat the steps and type 3 in place of 2 to get location value

excel-vlookup-1

Pivot Tables: these are summary tables that help you perform aggregate functions like count, average, sum, and other calculations according to the reference data that you have in your excel sheet. Excel 2013 onwards have Recommended PivotTables, which makes it even easier to create a table that displays the data you need.

To create a PivotTable manually, go to Insert > PivotTable and select your data range. The top half of the right-hand-side bar that appears has all your available fields, and the bottom half is the area you use to generate the table. For e.g. to count the number of companies location wise, put location column into the Row Labels tab and company name column into the Values section of your PivotTable. By default it shows count but you can choose many other functions in the Values dropdown box. Just play around with PivotTables with larger set of data. It is a perfect solution to slice and dice data based on various factors.

excel-pivottable-1

Filters: these help you look at data that matches a certain criteria. Excel filters are very helpful when you have large amount of data that you need to analyze. Suppose you have data headers in first row, you can add filters to every column in first row. Then you can filter out the required data by selecting the appropriate option. To add a filter, click the Data tab and selecting Filter. Once the filters are added, click on the down arrow and select data to be filtered. For e.g. if you want to see how many emails do I have from NY, then click location drop down, uncheck all other options except NY and hit Ok.

excel-filters-1

You may also select a combination of filters to available as Text Filters to further drill down data.

IF Formula: this formula is very handy when you want to evaluate value inside a cell under some criteria. If we want to input different information into a cell based on certain condition in another cell. The basic format is =IF(logical-condition, true-value, false-value). For e.g. if we want to display some value basis age then we can put the formula like =if(C2<40, "Young", "Middle")

excel-if-formula-1

Conditional Formatting: It allows you to change a cell's color based on the information within the cell and the criteria that you input. For e.g. if we want to color code red all cells where location is NY then select all the cells, select conditional formatting under Home menu. Next select appropriate criteria that you want to apply and the values.

excel-conditional-formatting-1

Transpose: if there is a lot of data in a spreadsheet and you want any specific column data to be copied in a single row or vice versa then this functionality is quite useful. It would take a lot of time to copy and paste each individual header. To do this, simply copy the data and right click on an empty cell, and select paste special. Then select transpose and hit ok. The data in column form would get transformed in row or data in a row would get copied into columns.

Text To Columns: suppose if you have data in a column that you want to split into multiple columns, then this functionality is very useful. For e.g. if you want to extract all domain names from given email column, we can use this functionality to split the email having primary part in 1 column and domain part in another column. This data is split at @ character. To do this, select the column to split, goto to data tab and click text to column. Next click delimited, check other box and put @ symbol. Give destination column other than source else data would get overwritten and hit finish. Now you will have data in 2 different columns having email split into primary and domain part.

excel-text-to-column-1

Dollar Sign: ever tried copying and pasted a cell data having a formula? Excel is intelligent enough to change references accordingly. When excel copies the formula, it converts that into relative formula. Now question is how to make the excel formula absolute? Dollar sign is used to convert any single reference or the entire formula as absolute. For e.g. if C1 cell has formula =(A1+B1) and is copied to cell C5 then the relative formula would become =(A5+B5). In order to have absolute formula, we add $ symbol so the formula becomes =($A$1+$B$1). Now if you copy this to any cell, it will always refer to cells A1 and B1. There are numerous occasions where we need excel absolute formula like we have 1 cell A1 with value 5% and we want it to multiply with all other cells, then we can simply use $A$1 everywhere.

excel-dollar-symbol-1

Excel Shortcuts: to navigate and process data quickly, you need to know excel shortcuts, which we have listed below.

  • Ctrl-Down/Up Arrow = Moves to the top or bottom cell of the current column
  • Ctrl-Left/Right Arrow = Moves to the cell furthest left or right in the current row
  • Ctrl-Shift-Down/Up Arrow = Selects all the cells above or below the current cell
  • Shift-F11 = Creates a new blank worksheet within your workbook
  • F2 = opens the cell for editing in the formula bar
  • Ctrl-Home = Navigates to cell A1
  • Ctrl-End = Navigates to the last cell that contains data
  • Alt-= Autosums the cells above the current cell
  • Alt+= Inserts a SUM formula
  • Ctrl+1 Displays the Format Cells dialog box
  • Ctrl+Page Up
  • Ctrl+Page Down Scroll between worksheets
  • Ctrl+D copies the cell above. Select a range or row and then Ctrl+D to copy the row.
  • Alt+Enter - Force a carriage return in a cell instead of wrapping the text while editing the cell.
  • Alt+DFF - to quickly add filters to the entire row

You may contact us to get expert excel advice or solutions. Please use contact us section and tell us what you need and we will provide solution.

Add comment