IT security news on the latest technology and the number one resource for your hardware and software needs.
Visit us at www.hyphenet.com

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.

“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.

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.

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.

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.

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.

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.

On the Home tab, in the Clipboard group, click Copy.

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 command.

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+