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+
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