Top Ten Keyboard Shortcuts for 2013

As you may know, every year I like to review my Current Favorite Excel Keyboard Shortcuts. This obviously changes over time as I learn new tricks and incorporate them into my daily work with Excel. New keyboard shortcuts can take a little time to get accustomed to, but it is almost always a worthwhile endeavor.

As I have said in the past, learning and using Keyboard Shortcuts in Excel will greatly enhance your speed and efficiency. Using shortcuts also will help relieve stress on your wrist from overuse of the mouse, and will Set You Apart from the Rank-and-File Excel Users.

So, without further ado, here in Reverse Order are my current favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2013:

10. F1 (Pulls up Excel Help – We can all use a little help now and then…)
9. F5 (Show the Go To menu – particularly useful with Named Ranges)
8. Ctrl + P (Pulls up the Print Dialog Box – Nice!)
7. Alt + + (Inserts AutoSum – Saves you from going to the toolbar)
6. Alt F (Pulls up your Files Menu – Very convenient!)
5. CTRL + 9 (Hides the selected rows – Cleans up your worksheet…)
4. F11 (Creates a Chart from your selection – Still an all-time favorite!)
3. Ctrl + N (Inserts a New Workbook – I use this often…)
2. Ctrl + Z (Undo Previous Action – Handy when you’re working fast!)

And My Number One Favorite Shortcut for 2013 is (this is even a little surprising to me…):

1. Ctrl + [ (Reveals Direct Dependents – Incredibly useful for quick audits!)

That’s it as of April 24, 2013! Depending on the details of your own work, you may rank them differently, but you may well find some of them indispensable in the future.

Happy Excelling All!

Looks Matter!

Looking Good Matters. Simple as that. This applies as much to business as it does in any other facet of society. Somewhat curiously, there are a large number of Excel practitioners who completely Dismiss the importance of aesthetics, saying it is “the data that counts”.

Obviously, the viability of the data is of preeminent importance, but if no one looks at it or takes it seriously, what does that matter? Studies have repeatedly shown that Emotions play an important role in the users’ experience. They also tell a good many about Your Brand, product or service. Just look at the products by Apple.

When it comes to making Engaging Charts, the Design Ribbon, ushered in with Excel 2007, clearly gives you several ways to enhance the appearance of visually-displayed data. Among the possibilities that are readily are the following Options for you on this ribbon:

1. Change the Chart Type (piece of cake!)
2. Chart Layouts (Many ways to display titles, labels, legends, etc)
3. Chart Styles (A generous variety of color and 3-D options)

You can also go to the Format tab and find a great many ways to make your work stand out in a Sea of Mediocrity:

1. Shadows
2. Glow
3. Soft Edges
4. 3-D Format
5. 3-D Rotation

So, once again, why is this important? Well, consider this: If your audience is going to Understand the Message your chart is conveying, they are going to need to Be Engaged by it First! The two major ‘C’s of good communication:

1. Captivate Your Audience
2. Convey Your Message

Good Communication is what it is all about!

Database Best Practices

Using Databases is Essential to getting the most out of Excel. They are also quite often abused. It always amazes me how many otherwise gifted Excel users make Fundamental Errors when it comes to the proper use of databases. It is for this reason that today’s blog is precisely focused on the Three Best Practices for working with Databases.

Other Excel authors may go on and on about several ways to make your data more accessible. If, however, you follow the following Indispensable Guidelines, you will be in excellent shape to slice and dice your data into Meaningful Information:

1. Do Not Mix Data with Calculated Fields
     a. Use a reporting page for any calculation

2. Avoid Blank Records
     a. Zero or N/A is better than blank

3. Break Down or Parse Your Information into the Smallest Discreet Portions Reasonable
     a. Use the Text-To-Column tool if needed

By following these Easily-Adapted practices when building your databases, you will be more readily able to use Pivot Tables, Boolean Functions, and other Excel Magic to extract the information you want. It all starts with Database Best Practices!

Cheers!

Analysis ToolPak

If you are an Excel user who is continuing to get deeper into data analysis in Excel, you will probably want to investigate an often-overlooked set of tools that are available. The Analysis ToolPak is an Add-In program that is available when you install Microsoft Office.

First of all, you need to load it onto your ribbon (Excel 2010 will be used for the example):

1. Click on File, and then click Options.

2. Select Add-Ins, and then in the Manage box, choose Excel Add-ins.

3. Click Go.

4. In the Add-Ins Available box, select the Analysis ToolPak check box, and then click OK. (See illustration):

You can then access a special Data Analysis group on your Data ribbon.

So, what sort of Cool Tools are available in the ToolPak? Among others, there are the following:

• Anova
• Correlation
• Covariance
• Descriptive Statistics
• Exponential Smoothing
• Fourier Analysis
• Histogram
• Moving Average
• Rank and Percentile

Each of the tools provides a very User-Friendly Wizard, and the results will likely be quite useful to any Analyst or Excel Guru-Wannabee.

Special Note: If you are using an older version of Excel, you should know that many Improvements were made, and new algorithms were created to improve the accuracy of the statistical, financial and math functions in ToolPak. That being said, the previous versions are quite suitable for a majority of business purposes.

The Analysis ToolPak. A worthy addition to your Excel Tool Belt!