Tuesday, March 18, 2014

Microsoft Excel tricks you need to know

Are you the type of Excel user that makes neat little table, or are you the Excel user that creates sophisticated charts that do magical tricks?

Microsoft Excel is a spreadsheet application developed by Microsoft that allows you to calculate, graph, create tables, and macro program with Visual Basic.

Here is a list to help improve your Excel skills and avail in the workplace.

VLOOKUP

VLOOKUP is a very powerful tool for Excel users.  With VLOOKUP, you can move data that is scattered across different sheets and workbooks to neatly organize your report.

The VLOOKUP functions stands for vertical lookup.   This function can be a marketer’s best friend and can save you hours of work.

excel-vlookup

 

“If” formulas

IF and IFERROR are the most useful IF formulas in Excel.  The IF formula lets you use conditional formulas that will calculate based on if something is true or false.

The IFERROR is a variant of the IF formula.  If you are doing a VLOOKUP to another table, the IFERROR formula will display in the field blank is the reference is not found.

excel-if-formula

PivotTables

PivotTables are a summary that allow you to count, average, sum, and perform calculations according to the reference points entered.  This data summery tool is found in data visualization programs such as spreadsheets or business intelligence software.


excel-pivot-table

 

PivotChart

The PivotChart lets you quickly and easily look at complex data sets to understand it more in detail.  They use interactive filters so you can browse through data subsets.

Excel 2013 added Recommended Pivot Charts, which gives you a preview hovering option that is very helpful.


excel-pivotcharts

 

Conditional Formatting

Excel’s extensive conditional formatting functionality lets you easily identify data points of interest.  This feature resides on the Home tab in the task-bar.

Example:  You’re grading test scores for students and want to highlight in red who’s scores have dropped significantly.  By using the Less Than conditional format, you can format cells that are less than -20.   These cells may be highlighted in a different color or display different text.


excel-conditional-formats

 

Transposing columns into rows and vice versa

Working with data formatted in columns and rows doesn’t have to be set in stone.  You can rearrange data into rows instead of columns, and quickly transpose data from one to another.
  1. On the worksheet, do the following:
    • To rearrange data from columns to rows, select the cells in the columns that contain the data.
    • To rearrange data from rows to columns, select the cells in the rows that contain the data.
  2. On the Home tab, in the Clipboard group, click Copy Button image.
Clipboard group on Excel Ribbon

Keyboard shortcut  To copy the selected data, you can also press CTRL+C.

 Note   You can only use the Copy command to rearrange the data. To complete this procedure successfully, do not use the Cut Button image command.
  1. On the worksheet, select the first cell of the destination rows or columns into which you want to rearrange the copied data.

Essential keyboard shortcuts

Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly. Here is a list of some favorites below.

Control + Down/Up Arrow = Moves to the top or bottom cell of the current column Control + Left/Right

Arrow = Moves to the cell furthest left or right in the current row

Control + Shift + Down/Up = 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

Control + Home = Navigates you to cell A1

Control + End = Navigates to the last cell that contains data

Alt + ‘=’ will auto sum the cells above the current cell

Excel is one of the best programs ever made for businesses.  It has remained the gold standard for almost all businesses worldwide. But whether you’re a newbie or a power user, there’s always something left to learn. What are your favorite function in Excel?  Please share with us in the comments below.

Don’t miss out on the latest tech news and computer security alerts!  Follow us on Twitter at @hyphenet,  “Like” us on Facebook or add us to your circle on Google+

References:

Real Excel power users know these 11 tricks - PC World
http://www.pcworld.com/article/2109084/real-excel-power-users-know-these-11-tricks.html

No comments:

Post a Comment