Tuesday, February 21, 2017

Control Yourself

The unassuming Ctrl Key gives you a lot of, well, Control.  It occupies our keyboards in both the left and right positions in the lower row, making it convenient to use whenever you need it.

So, what miracles reside in the Ctrl Key?  Quite a few…

1) Speedy Navigation: When you press Ctrl and any Arrow Key (up, down, left, or right), you Jump to the last populated cell in that direction. Bamm! Just like that!

2) Fill the Contents and Format: Using the topmost cell of a selected range as reference, click Ctrl+D to Fill the Selected cells below. It’s a true Crowd Pleaser, (and I’ll bet you’ve never tried it…).

3) Hide Your Columns:  Simply select the columns you wish to hide, and click Ctrl+0.  This totally Rocks!

4) Make Noncontiguous Selections: Select any cells you want, hold down the Ctrl key while you click on a cell or drag through a range of cells. As long as you hold down the Ctrl key, you can click and select Anything You Want on the spreadsheet.

5) Print Without the Mouse: Sometimes we still need to Print a worksheet. Rather than using the mouse, another simple Crowd Pleaser is to simply press Ctrl+P. Bamm! Ready to print!

There are a great many useful Ctrl Tricks that you can use to help you Control your worksheets. Take a few minutes some time, and give them a try. Control is a good thing…

Tuesday, February 14, 2017

Have it Your Way…

No, we’re not talking Burger King here.  The fact is that we have discussed various ways that you can customize Excel to suit your specific needs.
The Standard, Off-the-Rack Excel Setup suits most users just fine, as it provides the features that most people want.  With an analogy to camera buffs, it is sort of a Point-and-Shoot approach to spreadsheets.  Nothing fancy here.  Of course, as a reader of Excel blogs, you are not a typical Excel user.

Excel offers an abundance of choices in customization for the more sophisticated user. The current and recent past versions of Excel, for instance, offer a One-Stop-Shopping location to choose from a massive array of Excel Options. This location can quickly be found by clicking on File on the toolbar, and going to Options button in the lower-left corner.

There you will be presented with the following 10 Categories of options:
1.   General
2.   Formulas
3.   Proofing
4.   Save
5.   Language
6.   Advanced
7.   Customize Ribbon
8.   Quick Access Toolbar
9.   Add-Ins
10. Trust Center

As an illustration, two of my all-time favorite options, the Analysis ToolPak, and Microsoft Solver Add-in can be found and activated by going to the Add-Ins category. The “Toolpak” provides a short list of powerful analysis tools that nearly any knowledgeable Excel specialist will find valuable.  The “Solver” add-in enables you to harness the sheer number-crunching power of contemporary computers, and with a bit of care, will provide you Best-Case-Scenarios for a potentially endless variety of business needs (and besides that, it is Totally Cool!).

Taking just a bit of time to add a Customized Interface to your Excel setup can provide you a wealth of powerful advantages in the future. If you haven’t explored these potential treasures, take 5-10 minutes at the end of the day and see if there isn’t something there which can make your life with excel better and more convenient. Why not “Have it Your Way…”?

Tuesday, February 7, 2017

Squares, Powers, and Roots

Though use of Powers and Square Roots is not a frequent need in business, knowledge of how these functions can be used in Excel can be of particular value, especially in the Financial arena. Dedicated financial calculators, such as the Texas Instruments BA II Plus, are more accessible, of course, but some skills with powers and roots in Excel can come in handy (maybe even with your children’s homework…).

The following are some of these valuable features and some examples:

The Power Function
As with all of the functions we are discussing this week, the Power Function works well with directly inserted numbers or cell references, and adds more flexibility than using the caret symbol (^) to raise a value to a power.

The syntax of the Power function is quite simple. If you wish to raise 21 to the third power (resulting in 15,625), you would use the following:

=POWER( 25, 3 )

The POWER function also allows you to raise a value to a Fractional power. Let’s say you with to raise 25 to the power of 1/3:

=POWER( 25, 1/3 ) This would give you a result of approximately 2.92

Square Roots
The syntax of the Square Root function is also not terribly challenging. If you wish to find the Square Root of 25, you would use the following:

=SQRT( 25 ) This, of course, produces a value of 5.

SQRT cannot handle negative numbers (not surprising), but you can work around that annoyance by using the Absolute function as follows:

=SQRT( ABS( -25 )) Once again, the result is a value of 5.

Power and Square Root.  You may not have immediate need for these functions in Excel, but they are good to know should the need ever arise.


Tuesday, January 31, 2017

Oddly Mod…

Back a long, long time ago when I was attending Sheridan Elementary School, there was a popular gambling game among us boys that involved guessing “Odds” or “Evens” regarding the number of marbles the other boy held in his closed fist. Judging by how many times I spent my lunch money on buying new supplies of marbles to gamble with, I apparently wasn’t very good at the game.

Knowing whether entries are Odd or Even can also be helpful at times when working with your databases or tables.  Such instances can include street addresses, employee ID numbers, statistical studies, and several other pursuits.

So is there a Good Formula for identifying an Odd or Even number? As with so many things in Excel, there are several ways to do this, but the following are probably the most intuitive. Assuming your data is in Column A, you could use this formula and copy down your range:

1. =IF(MOD(A1,2)=0,"Even","Odd") This IF formula incorporates the MOD function to generate the results. MOD is an unusual function (It is so Mod, Man!) that finds the Remainder when you divide one figure by another.

~ Or ~

2. =IF((ISEVEN(A1) = TRUE), "Even", "Odd") This IF formula incorporates the (also a bit obscure) ISEVEN function to generate the desired results.

Odds and Evens: Knowing this information can be Just What You Need at times to ease your data analysis.  Now, anyone what to make a guess about the marbles in my fist?