Having It Your Way…

I think most of us would agree that Excel will, by default, usually pick the best option for how it operates. This is, of course, Not Always true, so the following are Three Great Tricks to make Excel do things Your Way.

Compatibility
Since you are an Excel Enthusiast, you probably have a comparatively current version of Excel. There are, of course, many people out there who are still using Office 2003 (or even earlier versions).

That being the case, sending them an .XLSX file will be a problem for them. You can easily remove this potential issue by setting your default save format as Excel 97-2003. Simply choose File / Options / Save / Excel 97-2003 Workbook.

Compress Your Images
When possible, it is good to add visual elements to what may otherwise be another dull spreadsheet. However, if you embed pictures or other graphical elements in your Excel spreadsheet, they can vastly increase the file size. If you need to email the spreadsheet, you can greatly reduce the size by simply compressing all the pictures in the worksheet.

Select one of the pictures and under Picture Tools. In the Adjust group, choose Compress Pictures. Select the level of compression you want to use and Badda-Bing, smaller file!

Use Manual Calculations
Here is a handy trick for saving computer resources. Although contemporary computers have a wealth of power, performing Excel calculations in real time on very large complex spreadsheets can eat up time and cause significant lags.

By default, Excel updates each value as you input numbers or change data. This is good in general, but if it is causing an issue, you can disable this feature by going to the Formulas tab of the ribbon and choosing Calculation Options / Manual from the Calculation group. To update your calculations, simply press F9 to make everything current. (The trick is, of course, not to forget this…)

Three handy tricks for Having It Your Way in Excel!

PowerPivot Updated!

Nearly everyone wants to be recognized as being “Someone Special”. Except for the hopelessly introverted, this is true among those of us who are into manipulating data in Excel.

One way to separate yourself from the Excel masses is to understand and deploy the use of PowerPivot. This tool gained popularity in Excel 2010, and is even more powerful and impressive in Excel 2013!

Although many of the features that were present in PowerPoint 2010 are now built into the Excel 2013 functionality, you still need to download the Free (“Free” is always cool) PowerPivot for 2013 add-in in the latest Excel version in order to do take advantage of some of the more advanced data modeling techniques.

Let’s say that you have several SQL databases housed on SharePoint and other sources, and you want to load the data and create interactive queries from within an Excel workbook. Quickly done with PowerPivot!

So, what can you do with this remarkable tool? Here is a partial list of some of the coolest features:

1. When importing data, you can filter out unnecessary data and import just a subset
2. Manage and create relationships using drag and drop in the Diagram View
3. Define your own calculated fields to use throughout a workbook
4. Define Key Performance Indicators (KPIs) to use in PivotTables
5. Author your own calculations using advanced formulas
6. Use other more advanced data and modeling operations

While this is a tool typically used by only Advanced Users, it is worth the time of any Excel Analyst to look at its possibilities. PowerPivot truly Empowers you to capture the data you need, gain greater insight into the meaning of the data, and do so without overtaxing your system’s resources.

PowerPivot: Not necessarily for everyone, but if you work for a large company and need a new way to Slice and Dice your data, take a few minutes sometime and check it out.

Next week: Back to the mainstream with some cool, easy techniques that will give you crunching options you never knew you had (and wish you did…).

Play It Again, Sam…

It is always fascinating to me to find Easily-Applied and Useful Shortcuts in Excel. Although today’s shortcut is Super-Easy to use, a quick survey of accomplished Excel Enthusiasts has revealed that few Excel users are aware of this handy tool. This just supports the well-founded knowledge that no one knows Everything about Excel!

While it is common and very intuitive to copy and paste Repetitive data to a set of noncontiguous cells (copy data, select the cells and paste), entering Newly Typed data doesn’t work quite as swimmingly (as the British may say…).

To enter Repetitive New Data into a series of noncontiguous cells, simply hold down the Ctrl key and select all of the cells into which you want to enter your new data. Then type the text you want to enter and (a little drum roll, please…) press Ctrl+Enter.

And, Here’s lookin’ at you, kid, Excel will enter the typed text into All of the Cells in your selection. Now, that’s really nice.

This is may seem a bit elementary, but as any long-time Excel user will testify, “ The fundamental things apply as time goes by.”

Cheers.

Avoiding Color-Coding Problems

Using Color is a very appealing way to format cells in your Excel worksheet. It makes the information you are analyzing and providing to other users visually apparent and adds life to what may otherwise be dull data.

Color-Coding can be problematical, however. If you wish to analyze your data by color, doing this can cause Unnecessary difficulties. Let’s say, for instance, that you want to produce the Average of cells based on color, (e.g. perhaps they represent Regions), and you have coded these cells with five different colors. How Do You Do This?

The VBA coding gurus would say, “Easy, you just write a complex code that you can link to a macro, and Badda-Bing, there you have it!” The fact is, of course, not many of us are comfortable using VBA, and this can also be an issue if the workbook is ever passed on to another user for maintenance.

The Best Solution is to Avoid the Problem entirely, while still making use of your Color-Coding scheme. Simply add an additional column, (which can be hidden, if you wish), and indicate the analysis criteria (this may be “Region”, “Status”, “Type”, “Customer”, whatever…) in this new field.

By constructing your spreadsheet with this additional column/field, you will then be able to easily use a simple AverageIf function (or whatever other function suits your purposes) and easily analyze your data. No VBA Required!

By avoiding the use of special programming, you will avoid potential problems in the future, and you will still be able to avail yourself of Visual AND Analytic Attributes.

In other words, you can have the Best of Both Worlds.  Cool…

Need More Room in a Cell? No Problem!

Happy New Year, All! I hope you are off to a Great Start in 2013!

As everyone know, the focus of Excel is numbers. There are, of course, times when you want to enter Text into cells as well. Sometimes you may even want to Add Another Paragraph (or perhaps just a line break) in a single cell. If you are working in Word, that’s easy – just hit the Enter key.

In Excel, however, hitting Enter will just take you to the next row below. “That is So Bogus!” you say, but take heart, the solution is so easy it will make you Laugh.

Now, there is typically More Than One Way to do just about anything in Excel. I am a big fan of using the keyboard whenever possible, but we will look at a couple of ways to enable a user to accomplish this Simple, but Useful trick. (Come to think of it, some people may describe me in those terms: Simple but Useful – Ha!).

First Technique:
Once you have selected the cell you will be entering the text, simply click the Wrap Text button in the Alignment group of the Home ribbon (Excel 2007 – Excel 2013).

Second (and Coolest!) Technique:
Using just the keyboard, all you need to do is press Alt+Enter to start a new line in the cell in which you are typing or editing. How Cool is That! Go ahead and see if your Excel Guru colleagues know this trick…

Cheers!