How to Hide Your Sheet

This week I want to talk a little about Excel and how to hide your sheets, but first a shameless plug.  I will be speaking at the South Florida SQL Saturday this Saturday, June 29th.  My first presentation will be on how to clean your data using SQL Server 2012 DQS (Data Quality Services).  Good clean data is vital when performing good data analysis with your favorite BI tool (SSAS or Excel with PowerPivot).  My second presentation will be an introductory presentation on using SSAS to build cubes.  For those who may say it is not different than working with PowerPivot in Excel, you are maybe 90% correct.  There are some differences, not just in the organization of the menus, but also in the way you build things.  But I’m not going to give everything away here.  Come to Ft Lauderdale to find out.

Ok, over the past 3 years, I’ve gained a new respect for the use of Excel in the work that I do.  As you know, it began with the introduction of PowerPivot for Excel.  Over the months I learned some new tricks when using Excel and I will share one with you today.  When using the original PowerPivot for Excel, every time I created a new Pivot Chart, I would get an extra sheet in my workbook which essentially built the data needed for the chart.  Normally I would not want to display that sheet to end-users because a) there is nothing for them to do on that sheet, and b) I did not want them to accidently change anything that might affect the chart.  I suppose at the time, the code needed that extra physical sheet in order to serve as the data source for the chart just like any other chart in Excel.  (In Excel 2013, that no longer appears to be the case, but keep reading anyway please.)

Anyway, I started to hide these extra sheets so the typical user would not know they were there.  Initially, I used a very basic way of hiding these sheets.  I would first open the Review ribbon and then select Protect Sheet from the Changes group.

ExcelSheet01

By protecting the worksheet with a password, even if a user found the sheet I was about to hide, they would not be able to open and modify anything on it.

Next I would right click on the sheet tab (at the bottom of the spreadsheet) and select Hide.

ExcelSheet02

This causes the sheet tab to disappear from the list of tabs at the bottom of the screen, but the data was still there and could be used by the chart.  Of course you could use this technique to hide any sheet in your workbook.

To restore a hidden sheet, all I would have to do was to right click on the tab and select Unhide and pick the sheet (if there is more than one hidden) from the dialog.  Obviously hiding a sheet provides no security.  That is why I first protect the sheet with a password.

ExcelSheet03

While this works fine for most cases, I found there was another way to hide a sheet.  Microsoft, in their infinite ability to name things clearly, calls this Very Hidden.  To do this, you must go to the Developer ribbon.  If you do not see the Developer ribbon, you may not have turned it on.  To turn on the Developer ribbon, go to File à Options.  Then select Customize Ribbon.  Check the box before Developer in the Customize the ribbon Section and click OK.

ExcelSheet04

Now in the Developer ribbon, click on Properties in the Controls group.

ExcelSheet05

Find the property Visible which should be at the bottom of the list.  Open the dropdown box of possible values and select the value: 2 – xlSheetVeryHidden.  (Yes, I know.  This option is also very hidden.)

Immediately the screen will update and it may not appear that anything happened unless you notice that the sheet you were looking at is gone.  Another sheet is displayed and the original sheet’s tab does not appear anymore at the bottom with the other page tabs either.

ExcelSheet06

So how do you get the sheet back if you need it?  Again the steps are a bit hidden from normal users.  You begin by opening the VB Editor.  You can do this by pressing Alt-F11 or by selecting the Visual Basic icon in the Developer ribbon.  Next select sheet that was hidden from the Microsoft Excel Objects and look in the Properties window (open the Properties window by pressing F4 or by selecting it from the View menu).  Again find the Visible property and change it back to: -1 – xlSheetVisible.

ExcelSheet07

The sheet will now be visible again.  The bad news is that since I told everyone that reads this blog how to recover a very hidden sheet, I guess you will still want to use the Protect Sheet option with a password that I mentioned at the beginning of this article.

C’ya next time.

Advertisements

Send E-mails from Word

Send E-mails from Word

At a recent meeting at work the question came up about a person wanting IT to create a program that would mass mail to a list of people a common email.  Some people suggested we needed to create a .NET application.  Others suggested that we look for a 3rd party solution to buy that would do the mailings for us.  Being the resident renegade who believes in using first what we have rather than looking to the outside for a solution, I first asked…

“Does this list of people include only employees or must the email go to people on the outside?”  You see my first thought was defining a distribution group within Active Directory and then using that distribution group to send out the emails.  But then they told me that most of the emails would be going to people outside of the organization.  Well, it would not make sense to add non-employees to our Active Directory so I suggested…

“What about doing a mail merge from an Access database or an Excel spreadsheet?”  No one was sure if that would really work.  However I remember doing mail merge several versions back in Microsoft Word and thought that it would at least be worth a bit of research.

After the meeting ended, I went back to my desk and started to play around a little.  This is the solution I found:

First I opened Excel and created a simple spreadsheet with the columns that I would need for the mail merge.  The first row consisted of a header with the names of the fields that I would reference from the mail merge.  Finally, I converted the range of cells to a table with the result looking like the following before saving it.

MailMerge01

Then I opened Microsoft Word.  One of the ribbons that few people ever look at, much less know what it includes is the Mailings ribbon.  The following figure shows a portion of that ribbon.

MailMerge02

The command group I want to focus on first is the Start Mail Merge group, specifically, the Start Mail Merge option.  From this dropdown menu, I selected E-Mail Messages since I wanted to send email to the people in my merge file.

MailMerge03

Next I clicked the Select Recipients button from the same ribbon group.  From the dropdown list, I chose the option to: Use Existing List…

MailMerge04

This opened a dialog box that allowed me to navigate to and select the Excel spreadsheet I created earlier with the names and email addresses of everyone I wanted to send the email to.

MailMerge05

After selecting an Excel spreadsheet, I still have to select which sheet in my workbook to find the data table.

MailMerge06

Now more of the commands on the Mailings ribbon become enabled.  Since this is an email, I want to add a Greeting line at the top of the email.  I could build this manually, but there is an option in the ribbon that will do this for me.  Specifically, I want to click on the command button Greeting Line in the Write & Insert Fields group of the ribbon.

MailMerge07

This opens the Greeting Line dialog which shows me what fields it thinks I might want to use in the greeting line.  This is where naming your headings can save you step because it correctly identifies my First Name and Last Name columns.

MailMerge08

However, even if I had named the columns something else, I could click on the Match Fields button near the bottom of this dialog to open the Match Fields dialog to identify the fields needed for the Greeting line as shown here:

MailMerge09

This adds a field to my Word document which will become my email called <<GreetingLine>>.  But let me add the rest of my text for a simple email which might look like the following:

MailMerge10

The only think I need to do yet is to add the field for person’s phone number which I had in as a column in the Excel spreadsheet.  I can position the cursor where I want to add the field, then click on the lower half of the Insert Merge Field button.  This opens a dropdown list of the fields in my source data table.  I can simply select the Phone_Number field from this list.

MailMerge11

This completed my sample email.  To send the email, I next look at the Preview Results group.

MailMerge12

Here I can click on the Preview results button and the use the controls to the right of this button to step through each of the recipients.   The following figure shows a single example:

MailMerge13

If everything looks good, I can complete the merge process and send out the emails by clicking the Finish & Merge button in the finish group.  Note that this button also has a lower half which when you click there opens a dropdown.  From this dropdown, you can tell Word to send the  e-mail messages.

MailMerge14

That was it.  It only took a couple of minutes to develop this solution.  But most importantly, it cost nothing, was immediately available, and made further use of the tools we already owned.  What do you think?  Was it a good, quick solution?

C’ya next time.

Overpromise and Underdeliver

“Wait a minute,” you say, “you got that backwards.”  Do I?  What was the last major purchase you made as a result of a marketing person or perhaps a salesperson  touting how great the features of that item were and how it would transform your life.  Did it?  If it did, congratulations, but most of the time we feel a sense of disappointment after buying something expensive that does not live up to the hype we were told.  Perhaps it was that sports car that was going to attract all the girls, but instead attracted higher insurance premiums.  Perhaps it was that computer with the new dozen core processor promising to cut our work time to a fraction until we found out that our software only takes advantage of a single core no matter what.  Maybe it was the new cell phone that would allow you to eliminate the need for a home computer because it could do anything and everything a PC could do.  Then you found out that half of the memory was taken by the OS and pre-installed software and the rest of the memory was not only not expandable, but that only certain things could be relocated to the micro-SIM card.

I maintain that everyday people are out to sell us something by over-promising the features, the savings, or the efficiency of their product.  In the meantime, the manufacturing portion of the company appears to under-deliver the final result.  I suspect that it is not always the manufacturing department’s fault.  They may have built a very good product based on the specs and requirements given to them at the time the product was first designed.  However, in order to sell that product and meet their sales quotas, the marketing staff has to stretch the capabilities of the product just a little to make it sound like a better choice than a similar product from that other company down the street.

Did you ever notice how some movie trailers give away too much in the desperate desire to convince you to come to the movie theater so they can sell you a bucket of popcorn that could feed a small town in Africa and a soda that costs as much as a half dozen 2-liter bottles?  Some theaters even allow you to get free refills on that bucket of popcorn loaded with melted salted butter.  Why?  So you are thirsty enough to buy a second round of sodas of course.  Put aside that popcorn bucket with its ton of butter and riddle me this, do movie trailers sometimes give away so much of the most interesting action scenes, even some of the plot twists, that they effectively have overpromised the enjoyment you will get out of seeing the entire movie which then appears to under-deliver the entertainment value?

I just went with my work team on a team-building event to see the latest Star Trek movie.  (Yes it is a valid team builder because everything you need to know about working together as a a team you can learn from Star Trek.)   We made a point of not listening to the reviews before going and we tried to avoid the advertisements and trailers although we were less successful at that.  I’m not going to spoil the plot for you, but let me just say that the trailers in this case did not spoil the twists in the story line.  In fact, in some ways, we were totally misled as to what was going to happen by the trailer.  Whether that was accidental or on purpose, I do not know.  Go see the movie and then let me know here.

However, this movie and indeed, most of the entire Star Trek franchise revolves around the concept of under-promising and over-delivering.  No matter how badly the ship is damaged, Scotty somehow manages to pull it through.  Ok, he did admit back in one of the movies that the way he was able to maintain his title as a miracle worker was by doubling his estimates.  Also, no matter how high the odds are stacked against Captain Kirk, he somehow always manages to save the day.  In fact, the heroes in most movies have that same characteristic, the ability to over-deliver when it really matters.

How does this apply to your IT job?  The last time someone asked you to estimate the cost (time and materials) for a project, did you sit down with a few of the developers to design the system on paper to arrive at a realistic estimate of how long it would take to complete?  Did you take into account that the average developer may not actually be productive for a full 8 hours in every single day they are at work?  Did you factor in vacation time, sick time, holidays, the inevitable time lost due to meetings, and the need for real testing, documentation and training of end users, not just the time needed to develop the initial system?  How about adding a contingency on top of that for the changes to the project’s scope?

Sure it’s not a perfect science.  It is difficult to predict major impacts to a project such as key staff members leaving the company or the ‘special project’ that supersedes all other projects that the president of the company wants done yesterday.   Estimating is really an art that a manager only learns by experience, by knowing what their team is capable of, and by being able to look at the bigger picture, not just the lines of code needed for specific functions.

For that reason, you may want to under promise the features that the new software will support or the amount of time needed to build and deploy the software by carefully examining the needs and the wants.  Trying to do everything wanted may cause the project scope to expand turning it into Mission Impossible which for most companies is equivalent to the over-promise/under-deliver trap I mentioned earlier.  Rather postponing some of the wants until a version 2 might be a smart way to get a system out in front of the user to get a better idea based on their user experience which wants should have a higher priority in the next iteration of the software.  By delivering something that is useable and meets all the essential needs early rather than delaying the development to implement all the wants can buy your team user acceptance and credibility much like Scotty on the Enterprise.  And if you have time, add back some of those wants that you did not promise in the initial system.  In other words, over-deliver.