Links are Cool!

I think we all agree that Charts are Engaging, Readily Informative, and Cool. But is there a way to make them Even Cooler, you ask?  Oh, yeah, Man!

For instance, let’s say you have a chart of sales figures that is interactive relative to which sales representative you choose from a Dropdown Box.  An easy way to do this is by using Validation. Simply select the cell in which you want the dropdown, choose Validation / Allow List and then select a range for your Source of dropdown entries.  Presto! Instant DropDown Box!  By combining a dropdown and elements such as a VLookup function, you can create a powerful and interesting report in Excel

But, would it be possible to have the Title of the Chart Change to reflect the value chosen? Yes, indeed! (It is so easy, you’ll laugh…)

Here is How You Do This:

1.    Select the Chart Title

2.    Go to the Formula Bar and type an Equals Sign: “ = ”

3.    Then Select the Drop-Down Box Cell to which you want to link

4.    Note: The final cell reference formula should look something like: “=Sheet1!$B$3”

How Totally Cool is that?!?!  Now every time you change the Name or Value in the Drop-Down, the Chart not only updates its graphical display of data, it Automatically Updates its Title! 

With this trick you can WOW your boss, your colleagues, your employees, even your dog or cat!  Give it a try!

Happy New Year, All!

Graphics in Excel

Excel users can be a funny lot.  Many of us tend to think of anything outside of cold, hard data is a waste of time and, moreover, rather frivolous.  Data and, more importantly, Information is, of course, what Excel is all about.  The thing is, though, it doesn’t have to be dull.

Excel is actually loaded with an array of graphics tools that can make your spreadsheets more engaging and readily informative.  Let’s look at some ways this can be done:

 Interesting Charts

Using some lively graphics can add real Pop to an otherwise sufficient chart.  Plotting Surfing Days in Southern California?  Use Surfboards in your chart!

 

SmartArt

You can find SmartArt in the Illustrations group on the Insert ribbon.  SmartArt quickly enables you to create diagrams of Org Charts, Processes, Cycles, and much more.

 

WordArt

Enter some eye-catching stylized text with Word Art objects.  Just click on the WordArt dropdown button from the Text group and choose a style that appeals to you.  You can resize and drag this text to any part of your worksheet.

 

Shapes

Let’s not forget the readymade shapes that add Functionality, as well as engaging panache to your Excel workbooks.  You can add Words and Hyperlinks to these easily-created shapes, enabling the user to navigate to anywhere you wish them to go

There are several other ways to add graphics to your Excel masterpieces, of course, and they can indeed add a touch of brilliance to your work.  I encourage you to try them out on your next report.  I’m sure your users will approve!

Happy Holidays All!

Medical Records and Excel

Having been diagnosed with a bit of pneumonia a couple of days ago, and getting a laundry list of medications to manage, it occurred to me that Excel would likely be a good friend in this regard.  So I created a simple spreadsheet/checklist to help me keep track of the what and when of my meds.

But wait!  Excel can do so much more: A brief amount of investigation revealed that, indeed, Excel offers a great deal of potential premade assistance to individuals wanting to track their medical concerns. Included in this group are the following templates:

·       Weight Loss Tracker with BMI

·       Blood Pressure Tracker

·       Calorie Amortization Schedule

·       Blood Sugar Tracker

·       Exercise Planner

·       Fitness Progress Tracker

There are many third-party predesigned dashboards, databases, and templates created for medical professionals as well.  A few of the most notable are those from:

·       Chandoo

·       Softonic

·       Tableau Software

In addition to a these out-of-the-box suggested formats, good old Pivot tables can be a tremendous boon, especially for medical professionals.

There are applications in Excel that can touch a myriad of facets of our personal, as well as our professional lives.  This is simply one more way that Excel can be of assistance.

Taking Polls and Grading Quizzes

Interactivity is a cornerstone to creating engaging, dynamic Excel workbooks and tools.  Option Buttons are one way to add this ability to your Excel creations, and make them more professional in the process.

Option Buttons can also be very useful in creating Quizzes in Excel, and although they can at first appear a little intimidating, they are really not too difficult to create.  The following is a step-by-step explanation of how to create your first quiz using Option Buttons:

1.   Add the Developer tab on your toolbar:

a.   Click the File tab and then click Options, and then click the Customize Ribbon category.

b.   In the Main Tabs list, select the Developer check box, and then click OK

2.   Under the Developer tab, choose Insert/Form Controls/Option Button

3.   Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4.   Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box from the Form Control group

5.   Then draw your Group Box all the way around your Option Buttons

Three More Steps, and We are Done:

6.   Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7.   Now comes the Cool Stuff: Create a Formula that is based on the value that is shown in the Cell Link

8.   For example, let’s say you have linked three Option Buttons to Cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF($E$5=2, "Correct!", "Sorry, Incorrect"))

 

 

Now when the quiz-taker chooses Answer 2 of the three possible, they are rewarded with the “Correct!” feedback.  Option Buttons, another way to make Excel do things you never thought possible!

The Cloud Rules!

Let’s face it, tablets and so-called phablets (a term I find curiously disturbing) are where the world is today, and there is an ever-increasing amount of Real Work done on these devices.

The Cloud is making all of this practical and efficient, and this is very much the case with Excel.  When it comes to Microsoft, OneDrive has replaced the previously-called SkyDrive, and it has become easier and more intuitive than ever to use.

 

By placing your Excel workbooks on OneDrive, they can be shared on any of your devices.  All you need to do is save your Excel file to your OneDrive and it is there whenever and wherever you need it.  Since I commonly switch from a conventional PC to a laptop to an iPad during the day, I am finding the capacity to do this a Great Convenience!  

 

That’s not all, of course.  In addition to easily saving your Excel masterpiece to OneDrive and accessing it on your other devices, you can also Share It with the World if you so choose.  Simply click Share on the left panel, save it to OneDrive and then complete the Invite People feature to provide them access to your work.  What makes this Especially Cool is that the other parties with whom you are sharing Do Not even have to have Excel on their computer!  This is a great attribute, as not everyone has Excel on All of their devices (the Luddites – Ha!).

Since the learning curve is now not at all steep for creating/modifying Excel workbooks on tablets, the opportunity is presented to not only share your work, but also work on your work while on a tablet, phablet, or maybe in the not-so-distant future, on a smartphone (of course, this may require some very keen eyesight…).  Is it all about work, work, work, you say?  Well, perhaps you have a point.  In my next blog I’ll show you something really Fun (and perhaps a bit useless) to do in Excel.

 

Cheers!