No IFs About It…

There are no “IFs” about it, the “IF” Functions are some of the most useful and accessible utensils in Excel. In fact, the knowledge of IF functions is so critical to any Excel users set of tools that it bears an occasional review.

There are, of course, several types of IF to choose from in Excel. Each of these powerful gizmos can aid you in making sense of your data. Here are my current 5 Favorite Ifs:

1.  COUNTIF: =COUNTIF(Range, Criteria) 
For the Criteria, you can specify a cell, a simple number such as 10, or use a greater-than or less-than criteria such as “>50”. Be sure to use the quotation marks if doing a great-than or less-than analysis. Alternatively, you can refer to a cell that contains your criteria number (No quotation marks needed with that approach).

2.  SUMIF: =SUMIF(Range, Criteria, SumRange) 
The Criteria is the same as in COUNTIF, but you have the option of applying it to a “SumRange”. These are the actual cells to add if the cells in the Range match the criteria. If SumRange is omitted, the cells in Range are evaluated by criteria and added accordingly.

3.  SUMIFS: =SUMIFS(Sum_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], ...)

First introduced in Excel 2007, but often overlooked, this function is similar to SUMIF, but more powerful as you can add multiple criteria.

4.  IF: =IF(Condition, Value If True, Value If False) 
This function is extremely valuable as a stand-alone or in conjunction with other “nested” functions. You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. IF(A1>70, “Pass”, “Fail”). Just be sure to use quotation marks if inserting actual words. 

5.   IFERROR: =IFERROR(Value, Value If Error) 
Introduced for the first time in Excel 2007, this combines the IF and ISERROR functions, and essentially gives you greater control with potential Error Messages. Rather than just accepting the sometimes cryptic and less-than-helpful default error messages that Excel dishes out, you can Customize your error messages for more clarity.

 No IFs about it, the IF functions are another great set of tools for Excel arsenal. Happy Holidays, All!

Keep it Safe!

You may have never done this.  In fact, the chances are very good that you haven’t.  Protecting your Excel work. Really, you say? How boring can a topic be!

You’ve never had a problem and, frankly, you can’t be bothered. Well, that may be just fine if you are the only one using your Excel masterworks, but if are sharing your work (and most of us probably are) with others there will come a time when the Others (note the ominous tone) will want to “Experiment” with your formulas and format. The construction of your workbook may have taken many hours to create, and there is the potential for substantial ruin. This does not need to be the case!

Excel has built-in Protection Tools to help assure that your Excel work is safe.

Let’s take a look at Excel 2016 for a How-To Example (other versions are similar): 

Protecting and Unprotecting a Worksheet with a Password

1. If there are specific cells that you wish to enable users to modify (such as a Data Entry Range in a dynamic report), go to the Review tab and select the Allow Users to Edit Ranges in the Changes group and select the range you wish to keep accessible. In the example below, cells B5:B14…

2. Next, click the Protect Sheet button in the same dialogue box. Excel in turn opens a Protect Sheet dialog box (see below), where you can Assign a Password, and select the Permissions you wish to be available to the users.

3. Click OK

You can easily Unprotect the worksheet with the password anytime you wish to make changes. And, of course, as this can cause a business disaster (people have been fired for losing this), Be Sure to Keep Track of the Password. This barely warrants mentioning, but it does happen.

One Last Important Note: Protecting your worksheets is Not a way of making them Absolutely Secure.  It is not ample protection to prevent users from accessing confidential or sensitive data, and any backyard hacker can break it.  It is for casual protection, which is for most workplaces, good enough…

Protecting Your Worksheets.  This is a Best Practice for any Excel practitioner, and one worth your time. There will be a time when you will be glad you did this.

Merry Christmas, All!

Dates - Have It Your Way!

Dates can be curious animals in Excel, and for most Excel users, they bear a closer look.  We are all familiar with the mm/dd/yyyy date format, but it is not “All Things to All Users”.  It is, in fact, very convenient to use Custom Date Formats in Excel, and it is easy to set up custom formats on your worksheet. Let’s look at some Practical and Useful Date Formats.

The most common Date Formats are, of course, as follows:

•   m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
•   d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But let’s assume you want to create a Date Format that is Not included in the built-in list in Excel. What do you do then?  Let’s say that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps to Achieve this Format are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example above, the outcome will be a date format in the syntax: yyyy/dd/mmmm.

You may not have a current need to control your Date Formats, but if it ever arises, you now know it is easy in Excel. I am sure we all agree that Better Communication (often lacking in business) is always a worthy goal. 

Being Proper

Importing data, and especially names, from outside databases can initially result in less-than-ideal formats in Excel.  Having a list of names imported from a source other than Excel can result in all upper-case, all lower-case, or even a mixture of both!

Now, this is not a big deal when it comes to analyzing the data, (in fact, some nerdy sorts seem to take pride in ignoring Proper capitalization), but it lacks a professional touch that can be easily corrected.

It would be quite absurd to consider changing the capitalization of the names Manually (how ridiculous would that be…). So what do you do when you download 7,000 names, and you want to change them to the Proper case?  The PROPER function comes to the rescue!

To illustrate this, let’s say that you have imported a database of your company’s employee names, and you have put them into a list that runs from A2 to A7002. In cell B2, insert the following simple formula:

=PROPER(A2) 

Give the handle in the lower-right corner of cell B2 a quick double-click to copy this simple formula down to the bottom of your list, and Bamm! Proper Names! 

One Final Note, if your database list contains names such as McElroy or DeLeon in it, you will probably need to change those manually. And if you have any Really Odd Names like DeLaMartre (my own…), then you will most surely need to make some adjustments by hand.  Ah, well, nothing’s perfect. You could, of course, come up with a sophisticated IF formula to handle these cases, but you may need to weigh the effort versus the results. Ah, well, nothing’s perfect.  

Gantt Charts

Gantt Charts have been around for a long time, and there are many software solutions available for companies of all sizes. Along with Microsoft Project, there are several specialty software solutions for producing these charts.  For many, however, good old Excel handles this task quite well.

You say you are not familiar with Gantt Charts?  Here is what they are all about.

First of all, they are a comparatively simple bar charts that illustrate and track the evolution of a project.  They allow the user to Juggle time and resources to obtain desired results. Developed by a management consultant by the name of Henry Gantt back in the early 20th century.  These robust charts clarify the start and finish dates of a project’s elements, and can be easily grasped at a quick glance (the hallmark of an effective chart). Although they were considered revolutionary at the time old Henry developed them, Gantt charts are considered mainstream today.

When used correctly and consistently (some individuals have been known to fudge with the results in the past), Gantt Charts can be invaluable tools to managers, analysts, and employees in the front lines.  Here is how to create one in Excel:

1   1. Open a New workbook in Excel (version 2013 or later is recommended)

2   2. Type in “Gantt” in the Search for online templates box

C   3. Choose the Project Planner template and click Create

The Project Planner Gantt chart template will then open, and you can customize it to suit your business needs.  Note: You can access Manage Rules under Conditional Formatting on the Home ribbon and revise the formatting and range of the chart.  I recommend that you modify the formatting, which can give you some control over the aesthetics.

With these very accessible Excel Gantt Charts, you can quickly see where each activity is according to plan.  Give it a try the next time a Big Project comes by, and enhance your Juggling skills!

Database Best Practices

Database Best Practices is always a worthy topic to review.  Assuring good database management includes several of these Best Practices and can they can take many forms.  One important Best Practice goal is to control and Eliminate Blanks in any of our database records.

Conditional Formatting is a familiar topic, and most of us have probably used it occasionally to highlight important information in our workbooks.  A unique method of using this tool takes a Reverse (or Negative) approach to this, however.  It is easy to see how this technique can significantly aid the goal of eliminating blanks.  Let’s look at how Reverse Conditional Formatting can be used to quickly highlight potential discrepancies.

For instance, let’s say you have a worksheet in which you are entering data (it may be numbers, text, or mixed) in a field, and you want it to be Very Apparent if a cell within that column is Blank). Here is a convenient way to use conditional formatting to do this:

In our simple example, let’s say you are going to be putting data in the short range of A1:A16.

1. Your first step is to apply a Fill Color to your range (A dark red, blue, or gray are good choices.)
2. Then select your range and go to Conditional Formatting / New Formatting Rule
3. Choose Use a formula to determine which cells to format and put the following:
4. =IF(NOT(A1=""), TRUE, FALSE)
5. Finally, for your Format, use Fill / No Color

Now whenever you place data (numbers, text, or mixed) in one of your cells in the range, the dark Fill Color is Cleared!

The cells that do NOT have data entered will retain the Original Dark Fill Color which will most certainly draw your attention to a possible problem.  Using this approach will help assure that you are maintaining a clean, viable database that will in turn enable you to extract accurate information, and that obviously, is what it is all about…

Templates for Real Work

Convenience means saving time and money. It also means a reduction in stress, and I am sure we all agree, that is a very good thing.

When it comes to Excel, using Custom Templates is one way of achieving such

convenience. Templates are not used nearly as much as they probably should be.  This is unfortunate, as these predesigned worksheets can save you a huge amount of time, and make you look even more professional in the process.

Any Excel user can easily find thousands of Office.com prefab templates by simply going to File/New.  Choices range from Daily Work Schedules to Event Planning; College Accounting to Retirement Planning; Wedding Budgeting to Garden Planning; and much, much more.  You will probably be able to find a template to fit nearly any need you have, whether business or personal.

In addition to all the terrific prefab templates, you can create your own for your unique work environment. For instance, let’s say you work in an accounting department and you prefer to use the Currency option for formatting your worksheets. You can set this up manually each time, or you can utilize the custom Templates feature found in any remotely recent version of Excel.

A Suggested Approach for Our Accounting Professional:

1.   Open a blank worksheet in Excel

2.   Go to the Styles gallery on you Home ribbon

3.   Right-click the Normal style and choose Modify to display the Style options

4.   Click the large Format button, and make the new formatting choices you desire

Once your number (as well as any other changes) format changes are made, simply click on the File tab and choose Save As. Type in the File name of your choice (e.g. AccountingTemplate2016) and for the Save as type, choose Excel Template (*.xltx)

Now whenever you wish to create a new workbook, your new AccountingTemplate1 will be readily available under PERSONAL templates when you open Excel.

The Convenience of using Templates.  Save time, money, and unwanted stress today!

Dates, Dates, Dates

No, we are not talking dates and figs today…

As most experienced Excel masters will tell you, working with dates in Microsoft Excel can occasionally be a maddening experience for many users.  Upon closer inspection, however, there are some excellent Date/Time functions built into Excel, and one of the most versatile is the (cleverly named) “DATE” function.

The syntax of the DATE function is =DATE(Year, Month, Day), therefore if you enter as follows, =DATE(2016, 11, 8), it will return today’s date of November 8, 2016.

What is distinct about the DATE function is its Flexibility.  It can, for instance, accept inputs from cells, functions, and calculations. For example, =DATE(2010+1, 6, 17) returns June 17, 2011. =DATE(2016, 11, 8+47) returns December 25, 2016 (Merry Christmas!). Pretty Cool!

So let’s say that you have an interactive What If report that has cell B1 with a continuously updated current date, (The =TODAY() function is an obvious choice), and cell C1 displaying the date which is a variable of numbers of years in the future based on the value you place in cell A1. Your formula in C1 would look like the following:

=DATE(YEAR(B1) + A1, MONTH(B1), DAY(B1))

If today is November 8, 2016 and you have the number 4 in cell A1, the above formula would return November 8, 2020.

The DATE function is worth taking some time to get to know. Make it a “Date” to remember…

Navigation and the Ultimate Mouse

As most Excel Gurus will tell you, there are a great many ways to Navigate in his or her Excel workbooks.  Navigating can be a bit of drudgery, especially if you are working with very large worksheets. Not a concern if you are working with a worksheet with 20 records, but If you are working with one with 20,000 records, it’s a Big Deal!

Now, if you’ve been reading this blog for a length of time, you may know that I am a huge fan of using Keyboard Shortcuts for navigation. In fact, here are some of the more common moves you can make without ever touching the mouse:

1. Control / Down Arrow: Takes you to last cell in column with data
2. Control / Right Arrow: Takes you to last cell in row with data
3. Control / End: Takes you to the last row, column and cell
4. Control / Home: Returns to cell A1
And, of course, there are some very clever moves with you can make with any Mouse, and even in certain cases the Name Box.

Speaking of the Mouse (this is not an advertisement, just an observation…), computer mice have come a long way over the years. In search of the Perfect Mouse, I have gone through countless designs and models from nearly all the major manufacturers. None are “Perfect” of course, but I recently acquired what I consider to be the Best Mouse I have ever used in Excel.

The mouse is the Logitech MX Master.  It offers smooth operation, customization options, and good ergonomics. What I particularly like about it, however, is the Thumbwheel that lets you scroll side-to-side.  This enables you to rapidly Navigate right or left on your worksheet, just like the ubiquitous scroll wheel lets you Navigate vertically.  The only drawback that gripes people, is that it is a bit expensive.

Whether using keyboard shortcuts, mouse tricks, or the latest in computer accessory gear, it’s all about Navigation.  Happy journeys!

Count (Dracula?)

No, we are not talking about the infamous Count Dracula this week, but since Halloween is next Monday, we most certainly are going to discuss the varied and highly utile Count functions (and have a little fun with Dracula along the way…)!

There are several major types of Count Functions. In summary, they are:

1. COUNT: Simply counts the number of cells in a range that contain a number
2. COUNTA: Counts the number of non-blank cells in a range (including those with text)
3. COUNTBLANK: Counts the number of empty cells
4. COUNTIF: Flexible counting function (this can be a little tricky, so we will explore with some examples)

The COUNTIF function is a Wickedly Useful, and sometimes Tricky (Hey, next week is Halloween), method to derive valuable information from your data, so we are going to look at some examples (I think you will find them a real Treat!)

The reason this function can be challenging to some users is because of the prevalent use of Quotation Marks within the formulas. The following examples will demonstrate how it works (Note: In each case, the formula uses a range named Data):

Count the number of cells that contain the first name of Dracula - "Vlad" (BTW, not case sensitive):
=COUNTIF(Data,"Vlad")

Count the number of cells that contain the words "Vlad" and “Igor”:
=COUNTIF(Data,"Vlad") + COUNTIF(Data,"Igor")

Count the number of cells containing Any Text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 3-letter words:
=COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "V":
=COUNTIF(Data, “V*”)

Count the number of cells that contain a value Greater Than Or Equal to 13:
=COUNTIF(Data,">=13")

Count the number of cells that contain a value from 13 to 33:
=COUNTIF(Data,">=13")-COUNTIF(data,">33")

With a bit of ingenuity, COUNTIF can indeed be a Treat to use. So, this Halloween, don’t forget the Count!  (I am sure Vlad will be pleased…)

Seeking Your Goal

Goals are, with little doubt, what keeps businesses focused on objective for which the company was founded and a guidepost for maintaining profitability.  The classic management tool that is built in to Excel is, of course, Goal Seek.

Goal Seek is, of course, a “What If” tool enabling scenarios. What If scenarios are essential instruments of analysis for nearly any business, and Goal Seek allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. Goal Seek can save you an enormous amount of work, and make you look good in the process (and we all like to look “Good”…).

The following is a very Simple Example showing how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the corner office big wigs. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Supposition: Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)
1) Insert the number of Expected Calls into Cell B2
2) Add the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

This tool can, of course, be used to keener advantage with more complex scenarios, but our example demonstrates the overall concept and use of Goal Seek.  This handy utensil can be very useful for the boardroom or any strategic venue. Another vital addition to an Excel Guru’s tool belt!

Revisiting Intersections

As you may have gathered, I have a soft-spot in my heart (some might say my mind…) for obscure Excel tools and techniques. The Intersect Operator is most certainly one of those obscure tools, and it can be extremely helpful if you know how to use it.  

Although there are a great many ways to gather information from Excel databases and tables, some can involve rather cumbersome formulas that can take a significant amount of time to construct and verify.  On the other hand, the Intersect Operator, is not only Powerful and Versatile, it is also quite Effortless to use (“Effortless” is always a good thing…)!

This handy tool uses the vertical and horizontal ranges in a cross-tab Table or Database, and finds the value at the Intersection (ergo, the Intersect Operator…).  The syntax (Special Note: Be sure to use No brackets or commas…) is simply:

= (RangeName1 RangeName2)

For the Ranges, you can use the generic names such as =(C2:C42 A10:K10).  This certainly gets the job done, but it is advantageous in many cases to use Named Ranges, as they are typically much more effective.

As you probably know, you can very quickly name all of the ranges in your database by selecting all cells (use Ctrl + A), and the click Ctrl + Shift + F3. This will bring up the Create Names dialogue box as shown below.  Just click OK, and Presto - Named Ranges!

 

Example: Once your ranges are Named, you can then find a value with the modest function (I’m using Cell E5 for the example):

=(Los_Angeles Quarter3)  Note: Be sure to include the space between the column title and the row title.

 

There are many, many ways of obtaining this information in Excel, but the Intersect Operator is certainly an important technique to keep in mind.  It may be an “Effortless” way to get the information you are looking for.

Every Picture Tells a Story

Charts are the quintessential graphical way to display your Excel data and information. There is no doubt about that. The fact is, however, that there are times when you want the fine print details as well. Happily, there is a truly excellent tool in Excel that enables you have both!

The Camera Tool 

Though not shown by default on the Excel ribbon, the Camera Tool can be enormously useful when you are presenting a chart, and would like to include a Resizable Image of the source data included within your graphical exhibit.  To gain ready access to this handy gizmo, simply go to the Quick Access Toolbar in the upper-left of your worksheet, and from the small drop down button, click More Commands.  Scroll down to Camera and click Add. 

Bamm! You now have the Camera Tool at your fingertips whenever you need it.

So how do you use this little piece of magic? Glad you asked!

1)  Select the data table of which you wish to use with your chart,  and Click the Camera Icon on your toolbar (just like taking a photo with a camera)

2)  Then, go to the area on the sheet on which you want the data to appear (probably a blank area on the chart), and Left-Click 

3)  Resize and Reposition until it makes you happy 

Why not just copy and paste, you ask? Because the image you captured with the Camera Tool is dynamically linked to the original! This is not just a static image, folks; Every time you update your data table, (and consequently your chart), your data image updates as well!

I guess the lines from the old Rod Stewart song were right, “Every picture tells a story, don't it…”

Charts and Anti-Charts

I believe we can all agree that Charts are a quintessential way of telling your story in Excel in a way that will be readily understood and appreciated. We have talked about modifying and enhancing the built-in options for charts, improving on the standard fare that we are all-too-familiar with.

Newer versions of Excel offer new, additional ways of making mini-charts that help to visually expand on often-dry, lifeless data on a line-by-ling basis.  Sparklines are a good example of the newer charting tools that are available.

Not all individuals or offices (corporations are often a bit slow in this regard) have the most update Excel versions to work with.  So what if you are using, (or your audience is using), previous versions of Excel such as 2003 or 2007? Is there a Dynamic Way to provide a Visual Display of the data without using a conventional chart?  Well, Yes, of course, (why else would I be asking the rhetorical question…).  By using the seldom used REPT function, you can produce an Anti-Chart that can provide you with a display that is immediately understood, dynamic, and easy to create. The REPT function repeats a text string the number of times you specify. For instance, the following formulas returns Five Asterisks: =REPT(“*”, 5)

The advantage of using this about this innovative is that it works in Any Excel Version, old or new.  You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.

For example, let’s say that you have a table with Months in Column A, Sales in Column B, and we want our Bar Chart (we’ll calling it an Anti-Chart, since we are not using a built-in chart option) in Column C. We can then insert the following function in our Chart Column C: =REPT(“*”, B2/1,000):

Noting that we are dividing the number in B2 by 1,000, this is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell C2 and drag it down to complete your visual illustration of your data. Presto - Anti-Chart! Give it a try!

Double-Click, Click, Click!

If you read this blog with any regularity, you know that I am a huge fan of Keyboard Shortcuts.

That being the case, I am nonetheless a proponent of using the mouse when it offers opportunities to augment your productivity.  Double-Clicking with your mouse is one such way to add speed and efficiency to your Excel work.

Here is an Updated List of My Favorite Ways to Use Double-Click:

 1. Adjust to Ideal Column Widths – Select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too. Perfect Solution!

 2. Auto-Fill a Series of Cells with Data or Formulas - Select the formula in first cell, Double-Click in the “handle” (small Black Square in bottom-right-corner) and Bamm! This works for formulas, auto-fills (of numbers, dates, etc) as long as the adjacent column has data.

 3. Rename a Worksheet Tab: Double-clicking the tab allows you to immediately edit the name.

 4. Insert a Split - Double-Click just above scroll-bar to include a horizontal split; Works for a vertical split too, by clicking on the little bar shape next to the right of horizontal scroll-bar.

 5. Edit a Cell Formula: Double-click any cell to edit its contents in place (rather than using the formula bar.)

 6. Collapse Ribbon to Get More Space – One of my favorites. Just Double-Click on ribbon Menu Names.

 7. Lock Format Painter – Save a lot of Time by Double-Clicking on the Format Painter icon, making it Reusable. Great Solution!

 8. Jump to Last Row / Column in Table – Another gem: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Presto! You’re there.

 9. Pivot Table Drill-Down: Double-click on any data value within a pivot table to instantly create a new worksheet which will show the Underlying Records that comprise that value.

Keyboard shortcuts a Totally Cool, but Double-clicking with your mouse is also a powerful tool that can save you many hours of work. Try any or all of the nine techniques and see if you do not agree that Double-Click Rocks!

More Room, Please

In spite of the current Tiny House rage, it is safe to say that most of us prefer to have a bit More Room.  In addition to our abodes, this can apply also apply to Excel.

 As we all know, the focus of Excel is numbers. There are, of course, times when you need to enter some Text into cells as well, and 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 Wrong!” you say, but don’t despair, the solution is so easy it will make you Laugh with delight (or maybe just smile…).

As we also all know, 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 techniques to enable a user to accomplish this very Simple, but Useful trick.

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 2016).

Second (and Totally 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. Does that Rock or What! Ask the other Excel Gurus in this office if they know this trick (I bet they don’t…).

Excel Charts: Other Uses

Charts are, without a doubt, one of the Superstar tools in Excel.  They can take otherwise dull, confusing data, and make it Visually Exciting and Informative.  After all, if your hard work and data is not easily understood, it serves no good purpose.

Limiting the accessibility of these spirited charts for use only in Excel worksheets, however, is not making the most of what they can offer.  For instance, how many of us have suffered through mundane PowerPoint presentations showing rows of dreary data and narrated by a presenter that has the droning voice of a far-off engine?  PowerPoints can be so much better with the introduction of some lively Charts!

So how do you add a Chart to a PowerPoint presentation?  For a Static chart, simply select the chart in Excel, right-click copy it, and simply paste/embed it into your presentation.  It can then be resized and further modified if you wish.

But what if you want to make a dynamic link between your Excel worksheet and your PowerPoint, so that when your data and chart changes on your worksheet, it also changes in your presentation?  Merely copy your chart as you did before, but when you paste it, use the “Keep Source Formatting and Link Data (F)” option.  You then have the luxury of having nearly instantaneous changes in your PowerPoint whenever you have changes in your Excel worksheet.

 

Very Cool!  Use this elementary technique to enhance the overt Professionalism in your presentations and documents.

Watermarks in Excel

The use of watermarks in business has been prevalent for a very long time.  They serve many functions and are certainly useful in Excel.  An Excel user may wish to include a WATERMARK to indicate a Special Status of an Excel worksheet. For instance, you may wish to mark it PROOF, CONFIDENTIAL or DRAFT.  A watermark can be a quick and easy solution.

First, you may wish to create your own specific image.  In WordArt, you can easily create an image (such as DRAFT), upload it to an image-handling app, and save it as a .png file.  Then it is a simple matter of adding it to your Excel worksheet as a Background Image.

To add your Background Image/Watermark, simply do the following:

1. On the Toolbar, click on the Page Layout tab.
2. Go to the Page Setup group on the ribbon and click Background
3. Browse to the file in your computer or network, and double-click it
4. Bamm!  Your Watermark has been inserted!

One Additional Suggestion:  Format your image that provides a proper Low Contrast with your Excel content. You don’t want the image to overpower and obscure the data in your worksheet.

This may take a few minutes to do this the first time, but the results are really quite effective. If you ever find the need to add a WATERMARK in your Excel worksheet, give it a try!