Slicers in Excel PivotTables

When Excel 2007 was introduced, the PivotTable Field List included a “Filter” area, where one could drop a field to use as a filter (for example, year, brand name, size, etc. from a block of sales data). If the user filtered for one year, or one brand, the name of the item was visible in the space above the body of the table. But if more than one item was filtered for, all it would say was “multiple items”…and not which ones. The feature worked, but couldn’t take this into account. So in Excel 2010, and all versions following, the Slicer feature was added. (No, I don’t know where they got the name. Sounded a little odd to me, too….)

Creating one or more slicers is dead simple. Make sure you’ve clicked somewhere in your PivotTable or –Chart, go to the PivotTable/Chart Tools Options tab, look in the Sort and Filter group, and click Insert Slicer.

Insert Slicer

You’ll need to choose which fields you want to filter with. Usually it’ll be at least a couple, one of which will often but not always contain values. And when you’ve selected, click OK.

Select Slicers

Done. Using the slicers is also easy. Click on the thing(s) you want to filter for (i.e. see), and they stay visible. Everything else hides.

But there is one neat extra tucked in there. Aside from the fact that the slicers print (they’re a WYSIWYG item), and you can resize them, you can change the number of columns of buttons per slicer, which means you can show more of them at once, and save some screen (and print) space.

Setting Columns

And that means you can use them to the fullest, because most of the time, any user who looks at your printout can see what you filtered for! No muss, no fuss.

Slicers are one of my more favorite features in Excel. They’re a good answer to a reasonable question (filters you can see and know what they’re filtering for), and they’re easy to use. They help content be more user-friendly, and even in a static situation (printout or PDF), they retain their usefulness.

Auto vs Manual Scheduling in Project

One small but important feature in MS Project is the Auto versus Manual Scheduling popup, in the Status Bar at bottom.

Here’s how it works:

Manual Scheduling allows the user to control start date, finish date, and therefore, duration. And the program will not change the dates of a manually scheduled task. Period. It might let you know if there are potential conflicts or problems with other tasks, but that’s up to you, as far as the program’s concerned. (If you can look at the project plan, especially in Gantt Chart view, most of those kinds of problems are fairly easy to spot, especially on adjacent or nearly adjacent tasks.)

Some people wonder, if this can become an issue so easily, why one might want to use it. The answer is, most project managers will start building the structure of a plan in Manual mode because it’s simpler, initially, not to have to worry about scheduling when the first goal is to get things written down and the basic sequence arranged. From what my students have told me, it’s often quite helpful to simply write the whole kit and caboodle down immediately, THEN worry about organizing and sequencing. (As one of my favorite literary characters said, “If you don’t write it down, it never happened.”)

Once that’s done, though, and the high-level tasks (or phases/stages) are in, switching to Auto mode is usually better. Because once you start linking tasks, and the plan starts really taking shape, you may still have some adjusting to do on dates and durations, but if the program can handle the basics, you the project manager can focus on the real trouble spots. “Which is, of course, the entire point.” (As Agent Smith said in THE MATRIX.) Auto mode, as you may gather, will allow more flex to a task and be more aware of adjustments that need making based on what’s going on around it—calendar specifics, constraints, and so on.

And since any task can be switched from Auto to Manual or vice versa at any time, you can fiddle with them whenever. You may not need to, but the program will not lock you in on this.

How to Normalize Data in Microsoft Access

The term “normalization” gets thrown about quite a bit in database circles, especially to try to explain to newer users a couple of principles of data organization, but seems rather vague to someone not acquainted with database-ese.

The idea of making data “normal” is not too far from the meaning used by database designers, when one understands what “normal” is. When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea of normalization. One wouldn’t want to put the first and last name in the same field, since we might want to sort by last name first. We couldn’t do it easily if the two were in the same place. Nor would we want to put street address, city, state, and zip code in the same field, for the same reason.

Bad Addresses

So one rule we try to follow regardless of what kind of database software we use, MS Access for example, is that we should put one piece of information in each field. A rough analogy would be, if one wanted to fill a glass jar’s volume completely, using something like golf balls or hard candies would not work too well, as they’re too big to really follow the shape of the jar. But using gravel, or sand, or better still, flour, would be better. The individual pieces are smaller, and can fit the space much better. The smaller the pieces, the more “flexible” they are in filling the space.

Jars

Secondly, it’s a good idea to divide the tables (or collections of data) into categories—an employee database might contain a table of personal data, a table of office data, one of health plan data, one of travel/transport data, and so on. Turns out it’s easier for most databases—and users—to work with a larger number of smaller tables than the other way round.

Finally, there is at least one exception. Some kinds of data, such as addresses, work better with a little de-normalization. (Access makes this fairly easy to see.) We don’t want to put a zip code in the same field with city and state, but putting city, state, and zip fields in an address table—even though slightly redundant—make the addresses much easier to understand and use.

Address Table

Using Effort Driven in Project

Even though Microsoft Project is fairly easy to use at the basic level–entering tasks and resources, assigning the one to the other, and fine-tuning a schedule–there are a few parts of the program that seem quirky, and can take a little practice to use comfortably.

One of these is the checkmark in the Task Information dialog box called “Effort driven”. The name is fairly self-explanatory, in that a task’s duration (the thing we normally concern ourselves with) can be affected by how much effort we want to put into the task, in the form of resources. Specifically, how *many* resources.

Effort driven checkbox

There is the quirk, though. This is how it works:

When the project manager first creates the task, it defaults to Fixed Units of work (stuffing envelopes for a wedding, or laying bricks for a wall would be good examples), and (if one chooses in the Program Options) Effort driven being on. The first time resources are assigned to the task, whether it’s one person, three, seven, or more, the duration won’t change. The program is assuming you know it takes, say, four days for six people to build a garden wall, and simply goes with the original duration. BUT…if, from then on, you change the number of resources (people, in this case), the program recalculates the number of days/weeks/etc. involved, as it assumes more people can do the task in less time, and vice versa. Not totally illogical.

Add resources and change duration

Nevertheless, there are some kinds of tasks for which the relationship doesn’t apply. If someone wants to interview a subject matter expert for three days prior to writing a manual, having one or two more people involved in the interview might help develop information, but probably won’t take substantially more time, or less.

So the trick, when one wants to add or remove resources without affecting duration in Project, is to open the Task Information box, go to the Advanced tab at top, and uncheck Effort driven FIRST. Then, go to the Resources tab, add or remove people or other resources, and OK out of the box. After which, if necessary, one can repeat the process and turn it on again.

On a more general level, depending on the task, the resources, and so on, one can remind oneself that it’s a good idea to go into the Task Information box->Advanced tab, and note the state of the checkbox before making changes.

Is there a suggested default? Hard to say. It depends on the nature of the projects and the tasks a project manager is involved in. A couple of guidelines might be:

  1. If the project will likely involve fairly frequent changes to resource assignment, leaving Effort driven off would probably cause fewer upsets to the schedule, though the manager would need to manually adjust it when needed.
  2. If the project resources are assigned and pretty much left in place, having Effort driven checked by default (again, this is an item in the Program Options which can be changed) would likely work in favor of the manager.

Program Options Effort Driven

Keeping the checkbox state in mind, then, can help avoid trouble when shifting resource assignments around.

Align Fields in Access Forms

Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things.
One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you’ve got it mostly cleaned up, the program has one particular nitpick some people don’t even notice. At first.

Form Design View
When you work on a form in Design view, the most time-consuming thing is sizing and moving fields (officially known as text boxes, where data show up) and labels (to tell you what the fields contain). And making sure the Tab key gets you from field to field in a logical order is important. Luckily, we can use the Tab Order dialog box to do this.
Tab Order Box

At this point, the tab order doesn’t match the order of the fields because we rearranged them. We want to tab left to right, top to bottom. So we arrange our fields, bring up the box, and click Auto Order.

Auto Order Button
BUT!…if any of the fields are not aligned just right, by their top edges, with their buddies in the same row, whoever’s highest gets dibs in the tab order. (“D’oh!”)
So we select the fields and labels in question, go to the Arrange tab->Sizing and Ordering group, and click the Align button. In the dropdown, we click Top to get everything in a given row to line up.

Align Top Command

Problem solved.
Then, go back to the Tab Order box, click Auto Order once more, and test. That should do it.
Yes, it’s kinda nitpicky. But it’s worth it, as getting this sort of thing right can improve data entry speed in Access, and other database programs quite a bit…and win you the approval and accolades of the people who do it.

Slide Master View in PowerPoint

One of the few parts of PowerPoint that isn’t quite intuitive is the Slide Master view. Getting to it is easy–go to the View tab–>Master Views group, and click Slide Master.

Then it gets a little more complicated.
To some people it looks as if they never left the Normal view–all that stuff over on the left side looks kinda like their slide show, but it isn’t. So what is it?

Those are the slide layouts you see in the Home tab–>Slides group–>Layout dropdown. In other words, those are the basic layouts you can use to create or give structure to any slides in your show. They’re built-in and always available.

Okay, first question answered. Then what?
Can we change them, or add to them? Sure. Usually the best thing to do is right-click and duplicate one whose setup is close to what you want, and modify the duplicate. Saves time and work. The same right-click gives you the ability to rename or delete one, so there’s your freedom of fiddling.
Wait a sec, though…what about the bigger slide at the top, the one all the others are connected to by the dotted line? Well, that’s the actual SLIDE MASTER, the Big Kahuna, le Grand Fromage (sorry, it slipped in).

And what does he do? His job is to control the appearance of the others. More specifically, the background elements (graphics, company logos, etc.) and the text appearance (fonts, sizes, colors, and so on). NOT the layout of the stuff you put on the slides.
So if you want all your slides to have a consistent appearance (highly desirable in almost all shows, business or other), do the basic visual stuff in the big boy at the top, then create or modify the smaller ones for the way you want the layout of the elements in the slides. And best to do it before inserting content, if you can. Less work on the back end. (Build the foundation before you build the house.)
What about other slide masters and layouts? Can we have more than one set in a slide show? We can. Whether it’s a good idea is another matter–usually one doesn’t want to mix too many slide masters/color schemes/etc. in one show unless they’re variations on one master theme, because it shouldn’t look like a circus came to town, but the program will allow it. So be conservative about such things. But don’t let it stop you from experimenting.

PowerPoint Tips and Tricks

…or, Some Ideas for Good Business Presentations

There are people who believe the joke that says, “We have met the enemy, and he is PowerPoint.” I don’t think so. But it is true that some people, through no fault of their own, don’t know where to start when it comes to creating a good PP presentation. And there are some things anyone can do to create a decent one.

GENERAL IDEAS

According to some sources I’ve seen, an officer in the USMC can brief a dozen subjects in under an hour. While not everyone is quite so succinct, it is a step in the right direction. Keeping the presentation simple and to the point is good. Less is more.

Not everyone in the audience may know the subject matter. Check on this if possible, and plan accordingly.

Format for readability, and for emphasis as needed. (If a company has product branding standards, they should be strongly evident.)

Keep to three or four bullet points per slide, max. More than that is like taking larger bites of food—one can choke. (Mentally, it’s similar to the Gary Larson cartoon wherein the student says, “Mr. Osborne, may I be excused? My brain is full.”) More slides with less content per is better than the opposite—a little at a time.

Make the text big enough to read easily. One of my former colleagues was a Naval aviator and had 20-20 perfect fighter pilot eyes. Not everyone does.

Use sans-serif fonts, like Arial, Helvetica, or Optima if possible—the less complicated letter shapes are easier to read on a monitor or projector screen. And stick to one or two fonts at most. Much less cluttered-looking.

Stick with as few colors as possible, too. Again, less busy-looking—and it won’t look as if the circus came to town. (That product branding thing can be helpful here.)

GRAPHICS, AUDIO, CHARTS

Graphics and audio can take up a lot of room on disk, and time to download, so use them sparingly, like seasoning on a meal.

(Audio can be a megabyte per minute if it’s good quality. DVD-level video can be four megs per second.)

Charts are useful, but they too need to be simple—if there’s a lot of information, create more charts. And make sure you label everything—simply.

This is a pretty good chart.

This isn’t. 😉

Pick the right kind of chart for the data. Pie charts for percentages, column or bar charts for sales figures, line charts for trends, etc. Trying a few different kinds is okay if you want to, but knowing what works for what most of the time gives you a foundation to work from.

Other elements in a chart can help, too. The company logo should be in any company presentation.

Photographs of people involved are also a good idea. (If you need a picture of someone outside the company, find out if you need a release form. Better to find out the easy way.)

TRANSITIONS AND ANIMATIONS

Be careful not to overdo these, either. If you go overboard here, the presentation can end up having the feeling of a Looney Toons cartoon. And that’s usually not desirable, even if the audience has a sense of humor, because it’s a distraction. One or two transitions, alternating, or a few variations on just one, and similar animations  throughout, will be more consistent, yet not boring.

And transitions should be “medium” or “fairly fast” speed—one to three seconds, so the audience can see the change but not be held in suspense (again, not really professional). This also give the presenter time to verbally segue from one to another.

There are a few more points that figure into good presentations, but these are pretty sturdy, and after hearing this sort of stuff bounced around in class for twenty-plus years, I think my students have the fundamentals solidly by now.

Track Changes in Microsoft Excel–Essentials

One of my wife’s colleagues recently asked about whether it’s possible to track changes in an Excel spreadsheet. It certainly is, though it works a little differently than in Word, say.
Turning the feature on in Excel is quite easy–simply go to the Review tab in the Ribbon, slide over to the Changes group, and click Track Changes.
Review Tab, Track Changes
Almost immediately, we see one of the differences between this and the Word version–we are told this will put the document into Shared mode, allowing others to open and edit the file at the same time. We also want to decide which specific changes to highlight.
Highlight Changes dialog box
Usually it’ll be When and Who. By not checking Where, we’re saying we want to see where *all* changes are being made, which is normally desirable. And we almost always want to track changes on screen, though it’s possible to list changes on a new sheet. (Sometimes, for business situations, it’s quite helpful to do the latter, but being able to track and accept or reject onscreen is often immediately useful.)
Once the feature is on, any changes we make will be tracked. A marker will appear at the upper left of the cell(s) in question, to let us know something’s different. Whenever we save, the markers disappear.
More names added
But the program still tracks changes. Whenever we’re ready, we can review them and decide to officially accept or reject them.
Accept-Reject dialog box
And if something changes after that..no matter how many times, the feature will still follow along.
Uncle Vlad added to list

So we can definitely track changes in Excel. The nature of the program requires that it function a little differently than in other members of the Microsoft Office, but not so much that the average user would have trouble with it.

The one downside to tracking changes in any of the Office programs is the feature is a little memory intensive. This isn’t nearly as much of a problem as it might have been a few years ago, what with the faster processors and less expensive memory we can get now.

Access vs Excel—Which should we use for what?

Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

How to Use COUNTBLANK in Excel

Some of the Excel functions, such as IF, come into play all the time, even outside business. They’re versatile and can do a lot. But others seem a little abstruse, or out in left field.

One question I occasionally get from business people in Excel is something like, “How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?” (Think “tax form” or W-4, for example. You want to make sure certain spaces are filled in regardless.)

In some situations, you might use a function like ISERROR, and incorporate it with IF to test if there IS an ERROR when you perform a calculation:

=IF(ISERROR(AVERAGE(D2:D10))…

…so that if performing the calculation glitches, you can have the formula show a message to this effect.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”…

Otherwise, perform the calculation.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”,AVERAGE(D2:D10))

Using ISERROR with AVERAGE

 

But this doesn’t quite solve the problem, because even if just one of the cells has data in it, there won’t be an error as far as Excel is concerned.

Formula finds one cell with data and calculates average

 

So we need to call a function that looks for ANY cells being empty, and lets us know. It’s called COUNTBLANK. (Sounds like some kind of strange nobleman–Count Blank, from some tiny hamlet somewhere….)

 

=IF(COUNTBLANK(D2:D10)>0,”Missing Data in D2 thru D10″,AVERAGE(D2:D10))

COUNTBLANK finds missing data and tells user

 

What it does is pretty simple, though. It COUNTs the BLANKs in a range, and can let us know how many there are. IF (as you see in the above formula) there are any (“COUNTBLANK(D2:D10)>0”), we want to see the TRUE result from the IF (the message about missing data); if not–that is, if the test is FALSE–then we want it to calculate the average. We’ve told the program to perform the calculation only IF the COUNT of BLANK cells is NOT greater than 0, and let us know if there are any blanks so we (or the user) can correct this.

COUNTBLANK finds no missing data

 

Of course, guaranteeing that only numbers get filled in is another matter. But there are a few different ways to take care of this, such as Data Validation. The important thing is, MISSING data are a problem, as the result isn’t an accurate one. And letting the user know about this is the big thing.

Microsoft Project: Formatting a Gantt Chart for Summary Task

Recently a student posed a question during a Microsoft Project class. How does one format the bars of a Gantt chart to look the same for each unique summary task? Something that may look like this.

slide-13

It begins with creating a custom flag field for each summary task. In Microsoft Project a flag field is a basic Boolean value. A simple yes/no field to identify further action. They are created using the Custom Fields button on the Format tab within the Gantt Chart Tools of the ribbon.

slide-2

The Custom Fields button opens a separate dialog box with many options. Our first choice will be to select flag from the drop down list of data fields in the upper right-hand corner. We are presented with twenty (20) flag fields. Our example uses five (5) and each one needs a unique name.

slide-3

That can be accomplished by selecting a flag, clicking rename, and supplying the new value.

slide-4

Once our five (5) flags have been named we can click OK to close the Custom Fields window.

slide-5

Next we need to mark our tasks as active (yes) or inactive (no) using the flags we just created. Insert a new column in the sheet view of the Gantt chart using one of our flag fields.

slide-6

The entire column defaults to no but we will select the first task of our named summary and change its value to yes.

slide-7

Once selected we can use the auto-fill handle (just like in Excel) to copy our yes across the remaining subtasks.

slide-8

We will insert a column for each of our summary tasks and repeat the process described above.

slide-9

The great thing is, once the tasks have been flagged we can hide the additional columns we just inserted.

slide-10

At this point we are in the home stretch. There is only one more step necessary to finish. We need some custom bar styles to match our flagged tasks and that is done by choosing the Format tab within the Gantt Chart Tools of the ribbon and selecting Bar Styles from the Format button in the Bar Styles group. You can also double-click a blank area within the chart side of the Gantt Chart View.

slide-11

In the Bar Styles dialog box we will scroll to the bottom of the list and add a new entry for each of our summary tasks using the name we gave to each flag. Shape, pattern, and color can be applied to each group and of course the flag number will be selected in the Show For … Tasks column.

slide-12

Once complete, clicking OK will supply our newly flagged and formatted Gantt bars with the shape, pattern, and color we just set up.

slide-13

Access 2013: Creating a Calculated Field

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.

create-query-design

add-tables

Once the table / tables are in place you can insert the fields necessary for your query.

insert-fields

The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.

zoom-tool

With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.

enter-code

Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.

save-the-query

calculated-field

Access 2013: Saving a Filter as a Query

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.

filter-number-filyers-greater-than

enter-a-value

filter-results

Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.

file-save-as-save-object-as

Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.

as-query

It really is that simple. The filter you just ran is now a query and can be used whenever necessary.

query-results

 

Changing Microsoft Projects Default Task Duration

In a recent Microsoft Project class a question arose concerning the default task duration, which is set in days. This student had recently been working on a deployment project and the default duration was too broad. It needed to focus on a smaller time-frame and it made perfect sense to set the task duration to hours.

An excellent question and the answer couldn’t be easier.

day-default-duration

To change the default duration for the tasks in a project we need to use the backstage options Microsoft Project makes available. By choosing the File tab and selection Options we are taken to the Project Options dialog box.

backstage

Once there we are presented with eleven options along the left side of the dialog box. Scheduling is the third option on the left and the one we are looking for.

duration-is-entered-in

In the Schedule section we will find Scheduling options for this project, and Duration is entered in will reveal the five choices we have:  minutes, hours, days, weeks, and months.

duration-length-options

Also notice that the Scheduling options for this project can be set for only this project or for All New Projects.

Once the duration has been changed simply click the OK button in the lower right corner and you will be returned to you open project. Any value entered in the duration column will now default to hours unless you specify otherwise.

duration-changed

 

Adding Power View to the Excel Ribbon

In recent years Microsoft has included several add-ins to boost Excels usage in the world of Business Intelligence. They include Power Map, Power Query, Power Pivot, and our topic of discussion today, Power View for Excel 2013.

Each of these ship with the Professional Edition of Excel 2013 but need to be added through the Options Panel. After going through this process I was more than surprised to find Power View was still unavailable. So this posting will help those of you experiencing the same dilemma I faced recently.

First things first. This is what my Insert tab looked like after the Power View add-in was…added in.

insert-tab-no-powerview

Normally there would be a Reports group that included one control, Power View. As you can see it doesn’t appear on this tab or any other tab.

Here are a couple of additional screen captures to show that it had been selected and does show up in the Add-In area of the Options Panel.

com-addins

options-dialog-box

I had gone through the necessary steps just as I had with Power Pivot, Power Map, and Power Query. They were all working yet Power View wasn’t available.

It took a while but the solution was found in a posting dealing with Excel 2016. Those steps are what we will go through now.

First we are going to customize the ribbon by adding a Reports group to the Insert tab. That is done by going to the File tab and selecting Customize Ribbon.

customize-ribbon

Make sure Insert is expanded and selected under Main Tabs, and then click the New Group button under the Customize the Ribbon section on the right side of the panel.

new-group

Now we will rename the group by clicking the Rename button in the same area as the New Group button.

rename-group

Now we need to find Power View in the Choose Commands from section on the left. It will be in the Commands not found in the ribbon menu option. It is Insert a Power View Report.

insert-a-power-view-report

That’s it. Click on OK at the bottom of the Options Panel and head back to the Insert tab. There it is on the far right side of the ribbon.

One additional thing, you will need Silverlight installed for Power View to work.

power-view-is-available

 

 

Microsoft Word – 10 Features that will Improve Your Workflow

With so many features packed inside each Microsoft application, it’s easy to overlook some that can really add value to your daily workflow.  Below is a list of some of the features you may have never noticed but will quickly become part of your daily routine.

Styles

image

Styles are not only a great time-saver, but they form the basis of a variety of other Word features.

Styles allow you to assign in bulk a variety of different formatting attributes, like font size, style, color, alignment, line spacing, borders, shades, indentation, etc…  The list is quite voluminous.  Once styles are applied to text, the text can then be bulk-updated simply by changing the style.  If you modify the style, Word will automatically apply the change to all text whose appearance was created from the style.  Imagine the time saved when making minor alterations like changing the font size of all headings.  Change the style and all of the headings are updated to match.

Once you have your styles in place, you can now also navigate more effectively through your document via the Navigation Pane.  Any text formatted with a heading style will appear as a list entry.  This will become one of the greatest time-savers when navigating large documents.

image (more…)

Excel – Hide Records with Zeroes in Pivot Table Calculations

One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?”  On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted.  There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated.  This is not an appealing prospect.

There is actually a very easy way to not display pivot table records that equal zero. (more…)

Microsoft Project – Display Estimated Durations with a Different Color in a Gantt Chart

When working with tasks in a project, it is common practice to display a duration as an estimate.  Displaying an estimated duration prepares the viewer for possible changes in scheduling.  An estimated duration takes the form of a question mark placed after the declared duration.

Estimated1

The issue is that some project viewers fail to notice the question mark; then when durations are updated, project viewers wonder why things have changed.  One way to ensure that people’s attention is drawn to the estimated durations is to change the color of the Gantt bars to reflect an estimated status.  There is no built-in state for displaying estimated durations in a separate color, but with a few short clicks this behavior can be achieved. (more…)

Excel Hidden Camera Tool – Great for Dashboards

If you are an Excel user who likes to create charts, design dashboards, or just likes to play with neat toys in Excel, this tutorial is going to be right up your alley.

Excel contains (in a super-secret place) a hidden camera.  “But why would I need a hidden camera in a spreadsheet program?”  I’m glad you asked.  If you have ever created a chart on one sheet, but you need the chard displayed simultaneously on a different sheet, and you don’t want to make two of the same thing, the camera tool will solve this problem.

First thing’s first; we have to find the camera before we can put it to creative use.

(more…)

12 Excel Keyboard Shortcuts for Every User

Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application.  It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all.  But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.)

The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts.  Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at all.

So let’s get this show on the road!

  1. CTRL+SHIFT+L – Turn On/Off Filter Controls

Filters are of tremendous use when analyzing large numbers of records in a table, but you are only interested in a select set of records that met a specific criteria.  Activating your filters is just a CTRL-SHIFT-L away.  This keyboard can also be used to turn off all of the filters and display the entire list.  (Filters are on by default when you convert a straight table to a Data Table, and not always desired.)  Finally, if you hit the “L” key twice (CTRL-SHIFT-L & L) you can effectively clear the current filters to start fresh with a new filter query. (more…)

Excel – Conditional Formatting with Subtotals

If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel.

If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline.

Before Subtotals

CondFmt1

After Subtotals

CondFmt2

But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit of pizazz. (more…)

Using Excel MODE Function to Return a Text Response

Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers.  But what if you want to return the most frequently occurring word in a list of words?

MODE with Numbers

Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often.

Mode1

In this list, the number “4” appears more often than any other number.

MODE with Words

As you can see, the MODE function does not work very well when pointing to a list of words.

Mode2

The function returns a “#N/A” error message.

Not to fear; MODE can be made to return words, but it take the combined efforts of SEVERAL functions, none of which are MODE!  (How odd does THAT sound?) (more…)

Excel – Mixed Pivot Table Layout

Excel – Mixed Pivot Table Layout

Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants.  Even the greatest of ideas are not without their issues.  Take for example the Report Layout feature in Pivot Tables.  Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data.

  • Compact Form
  • Outline Form
  • Tabular Form

Let’s examine what makes each layout unique.

Compact Form

All row-based data is combined into a single column, one row per line item, and indented to reflect the position within the hierarchy.

PivotLayout01 (more…)

Excel – Convert Names to Email Addresses

Converting Names into Email Addresses

Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses.

Flash Fill (Excel 2013 / Excel 2016)

If you are not familiar with Flash Fill, this tool allows you to type a pattern next to existing data and Flash Fill will repeat the pattern for the remaining data but on a per-record/per-line basis.

Let us take a look at the following example:

You have a list of first and last names and you wish to convert those names to an email format that takes the first letter of the first name, adds a “dot”, then adds the last name with an “@” sign and the company domain name. If we had an employee named “Fred Smith” who worked at “widget.com”, we would need to assign the email address “f.smith@widget.com” to the user.

Imagine a list like the following:

Convert01

(more…)