Fred Pryor Seminars Blog Thu, 12 Jan 2017 21:35:54 +0000 en-US hourly 1 Use Excel to Determine if Your Data Shows a Relevant Trend Thu, 12 Jan 2017 17:51:33 +0000 Problem:

You have a workbook that contains a full twelve months of production. Even after graphing the data, it still isn’t clear if production went up or down last year.


Insert a trendline. With a trendline, you can see if your sales went up, and if so, by how much. Charts reveal a great deal about your data in a dynamic and accessible format. Sometimes, there are swings in the data that make it difficult to discern if there is an important trend in the information. Trendlines perform calculations behind the scenes and provide an indicator of the direction your data is moving to help make the big picture clear.

Including a trendline in your charts may help illustrate both the size and direction of changes in your data. They are also useful in forecasting future or past values based on available data.

To add a Trendline to a chart:

  1. Right-click on any data bar in the chart and select Add Trendline from the menu that appears [A] to open the Format Trendline task pane [B].
  2. For a basic trendline indicating the general direction of your data, click the radio button to the left of Linear [C].
  3. Click the Display R-squared value on chart checkbox [D].
  4. Click the Close button [E] to close the task pane.
  5. Review your chart with the trendline in place [F].

Note the R-squared value on the trendline:

In the simplest terms, this describes the trendline’s accuracy. The closer a trend is to “1,” or 100%, the more accurate it is. Most trends should not be considered significant until they are at least 0.5, or 50%.

Try this: Click through the various trendline options in the Format Trendline task pane to preview how each option changes the trendline’s shape as it changes how your data is analyzed.

Pro Tip!

Add a trendline from the Chart Elements button by checking the Trendline checkbox on the Chart Elements pane [G]. Click the triangle to choose from a list of types of trendlines, or to open the Format Trendline task pane by clicking More Options…

]]> 0
Customize Spellcheck and Grammar Check Options in Excel Thu, 05 Jan 2017 16:33:05 +0000 While working in Excel, you may want to change which AutoCorrect / AutoFormat alerts you see (and don’t see) in the creation process. Luckily, you can customize the spelling and grammar checker by selecting specific options to employ (or not) as Excel checks your cell data.

Customize Spellcheck Settings

  1. Click the File tab.
  2. Click the Options button to launch the Options dialog box.
  3. Click the Proofing tab.
  4. Basic grammar options are found in the When correcting spelling in Microsoft Office Programs section. These apply to all Office applications, such as Word, Excel and PowerPoint. 
  5. To set options for Excel only, review options in the Change how Excel corrects and formats text as you type section by launching the AutoCorrect Options… window:
    • Correct Two Initial CApitals: If the shift key holds and you type two capital letters as part of a longer word, this corrects to just one capital.
    • Capitalize first letter of sentences: Automatically capitalize the first word in a sentence as you input text in a cell.
    • Capitalize names of days: Automatically corrects proper days of the week with a capital first letter.
    • Correct accidental use of cAPS LOCK key: If you attempt to type regularly with the caps lock on, Excel will detect this as an error and correct it.
    • Replace text as you type: Allows you to designate specific corrections.
      • Replace: Choose a shorthand or common error to be corrected
      • With: Type out how you’d like that corresponding shorthand or common error to appear instead.

NOTE: The Exceptions button opens the AutoCorrect Exceptions window. This window allows you to select exceptions for any active workbook. You can add or delete specific examples from this window.

]]> 0
Goal Setting Activities: From Process to Product to Outcome Fri, 30 Dec 2016 16:10:31 +0000 One challenge in creating goals is defining what the goal is. We generally have some sense that a goal is a future state or milestone, associated with some good thing. How do we translate that into goal statements that are specific, measurable, achievable, realistic and timely?

Here’s an example. The CS Company relies on subcontractors to directly provide services to customers.  Based on customer feedback, CS managers think there are inconsistencies in how these subcontractors deliver the services, but they aren’t entirely sure what to do about it. There have been several meetings to discuss the matter, but with no specific goals.

This is a great case study for understanding goal setting. In fact, CS could develop several possible goals, associated with process, products and outcomes.

  • A Process Goal: Define the Problem. Setting a goal to understand the problem itself is powerful. One goal could be to define the problem, if there is one. Defining the problem is an example of a process goal – the output is a problem statement, and a project plan for moving forward.
  • A Product Goal: Customer Survey. Defining the problem may require a product goal, like a customer survey. The survey would be designed to gather feedback from customers to validate any inconsistencies between the subcontractors, and if so, what the implication is. The survey results would be a product. The survey itself is not an outcome; however, setting interim goals helps you more clearly analyze situations, and determine subsequent goals.
  • A Product Goal: Subcontractor Training. The customer survey does reveal a problem in consistency between subcontractors, and the implication is that customers are not being fairly treated. This leads to a new product goal: a training program to teach subcontractors how to implement the requirements accurately and precisely. All subcontractors must complete the training by a certain date.
  • A Process Goal: Define Performance Measures. How will CS know the training has been successful? Training may be a product, but it does not necessarily lead to the desired outcomes. This requires a new process goal: defining the performance measures that will tell us that the training has been successful and that, in fact, it addressed the problem.
  • An Outcome Goal: Greater Consistency, More Satisfied Customers. In the end, the outcome-related goal is more consistency between subcontractors, as measured through some process (observation) or products (testing) and validated through future products (customer surveys).

This sequence highlights a key point in goal setting: you are never done. Solving one problem merely creates new problems, and the need for new goals. Goal setting is not a one-time activity – it is a continuously evolving need. Smart organizations view goal setting as a process and not an event.

]]> 0
Reflecting on Resolutions: How to Achieve Your Goals Fri, 30 Dec 2016 16:10:22 +0000 New Year’s resolutions are – for many – a repeated annual ritual.  This year, let’s pause for a process check. How many of those resolutions have you achieved?  If you can look back on past years and name the resolutions you have successfully completed, then good for you!

For many of us, however, resolutions are long forgotten, often by the beginning of February. We keep carrying those 20 pounds, we quietly stop going to the gym or we fall back into the same bad habits. And yet, we made those resolutions for a reason – there was something bothering us enough that we wanted to change it.  Why didn’t we follow through on those goals?

Here are some reasons that we fail to follow through with resolutions:

  • The perceived problem leading to the resolution was not powerful enough to drive change
  • The resolution was not tangible or tactical enough to become a concrete action plan
  • The benefits of achieving the resolution were not clear enough to motivate change
  • We never really expressed the resolution as a target – it was more of a hope or wish

How do we achieve more success with our resolutions in this new year?  Here are some ideas.

  • Be very clear about the pain caused by the status quo. Change is hard, so it is easier to make changes if we have a real reason to do so. Many think about the benefits that would come with achieving a resolution, but try instead to describe the pain associated with not changing. If you can’t really name a pain that makes you feel strongly about changing, either think more deeply about why the resolution is important, or change your resolution.
  • Be concrete – with actions and benefits. For many, resolutions are abstract thoughts or concepts that float through our mind in the final or first weeks of the year – I want to lose weight. I need to be nicer. I need to get out more. I need to spend less money. Instead, be concrete, naming specific concrete actions and the tangible benefits that are expected.
  • Write down resolutions and milestones. Writing down our resolutions makes us more accountable for them. Attaching specific tangible and achievable milestones and timelines – like with a project plan – can help us keep on track over time.
  • Celebrate what you are proud of. Call it a “reverse resolution.”  Perhaps instead of coming up with things to do differently, resolve to continue doing something you are proud of, that gives you joy and builds on your strengths. Continuing positive actions is a great way to start the new year.

Resolutions help us mark each new year – this year, resolve to make resolutions that stick with you!

]]> 0
A Goal Setting Worksheet: 9 Critical Questions Fri, 30 Dec 2016 16:10:12 +0000 Checklists are wonderful tools for systematically completing tasks. Filling out a familiar worksheet or answering a series of repeated questions helps train our mind to anticipate the process, making intimidating tasks more accessible.

With that in mind, here are 9 critical questions to help you with achieving goals.

  1. What’s the goal? What is this goal about? What is the subject, topic area or project name? This is the title of your goal setting document. (Examples: Increasing Attendance at Corporate Events; Completing the ABC Project for Client Y; Increasing Employee Engagement in Team C.)
  1. What’s the problem/need? What problem am I solving, or what need am I filling? In 20 words or less, write down a problem statement or need statement.
  1. Who cares? Why is solving the problem or filling the need important? Who is it important to? Write down the benefits of achieving the goal, and who these benefits will help.
  1. What does success look like? What is happening when my goal is met that is different from what is happening now? Write down your success criteria – what 3 measures show success.
  1. What am I creating? What is being generated or developed? What will I point to as the deliverables or products? Write down a concrete list of outputs that will be created as you work towards the goal.
  1. What is my plan? Now, reflect on the process or project plan that will lead to the deliverables you have listed. Be sure to include realistic dates, and partial deliverables if needed. (For example, you may want to create an outline before you create a full presentation.)
  1. Who else will be involved? Who will you count on to help you reach your goals? Whose permission do you need? Who can help you along the way? Which project beneficiaries can and should be involved in the process, and how will you engage them? List the key people, their interests, their roles and how you will connect with them.
  1. Will my plan address the problem or need? Look back at your list of deliverables and your plan. Are you still on track to solve the problem or fill the need?  Too often project plans become divorced from the original goal and intent.  Make sure that your planned actions still align with the problem or need at hand.
  1. What should I do right now? Every marathon begins with just one step, and a rolling stone gathers no moss. Finish your worksheet by listing three immediate action items that you can do in the next 24 hours to start you on the path forward.

These 9 questions will help you define your goal, describe the underlying problem or need and articulate the path forward. Go for gold in your goal setting!

]]> 0
Excel Template or Macro? When to use each! Thu, 15 Dec 2016 15:53:54 +0000

A lot of Excel users are confused about when to use macros and when to create templates.  A macro is a recording of formatting changes and other steps that can be replayed quickly. A template is a pre-formatted spreadsheet with headers and formulas – awaiting your data. I find that whichever one you know how to create, is the one you tend to use!  But just like you might be able to use a shoe for a hammer, it’s better to have the right tool for the job!

The goal of both is the same: To restructure the worksheet to fit your specific needs.  For example, Ben receives monthly invoices from three different vendors. He then reformats them into one worksheet and imports that into an Access database.

Some of the formatting changes include deleting blank rows, inserting a date column, naming a range, changing cell styles, removing duplicates and separating text into columns.  This is tedious work and Ben wants a faster way to do it.

The question is should he use a macro or a template?

Here is a good rule of thumb! Create a template when you create the spreadsheet and control the data entry.  If you already have the spreadsheet with all the formatting and structure, but it’s full of data, simply delete the data and under File, Save As, save the file as a template.  When it’s time to create the new worksheet, go to File, New and find the template under Personal.

Use a macro when you don’t have any control over how the data is originally formatted like Ben’s invoices from different vendors who are NOT going to change their invoice structure for one customer.  You can check out more in depth instruction on macros here. When you create a macro with lots of steps, you might consider creating several little macros and one final macro to make all the previous macros run in order.

You can create a macro that will automate just about any steps that you have.  And they are as easy to create as recording a voice mail message!  Go to the View tab, Macros, Record Macro. Give it a name, and the Stop Recording button appears on the status bar. Perform the steps that you want to record, then click the Stop Recording button. It’s that easy!  When it’s time to play the macro, go back to View tab, Macros, View Macros. Select the one you want and click Run.  All the steps run in order!

Bottom line is if you create and update the worksheets, use a template to create the structure.  If you receive the worksheets regularly and they are not set up the same, use a macro to restructure them.  That way you can have the right tool for either job!

]]> 0
Calculations Without the Formulas! Thu, 08 Dec 2016 13:46:33 +0000

Some people use Excel because they LIKE numbers and math.  Some use Excel because they DON’T! Nick counts on Excel to do the mathematical heavy lifting.  He needs to calculate rows of data, but the thought of creating formulas and then checking the answers seems daunting.  If you feel the same way, you might like the automatic calculations that Excel can do without you having to create one formula!

Imagine simply clicking on cells with numbers and the sum instantly appears! It is faster than looking for your calculator!  Once it is turned on, it is always available to view on the status bar every time you select multiple cells that contain data. This is great for a quick peek to see how different salespeople, offices or products are doing.

So, how do you turn it on? Right-click on the status bar (that’s the little toolbar below the worksheet tabs that says Ready). Then, click to check the calculations you want to appear.  It stays on until you turn it off in the same location.

This will simplify your year-to-date totals! Let’s say you keep track of the number of views on your website’s pages.  You normally create a formula to add the new month’s totals to last month’s totals to create the year-to-date.  You have to create a new formula for each new month throughout the year.

Instead of creating all those formulas, simply copy your new monthly total, then paste it over your year-to-date data. EXCEPT instead of using Ctrl + V to paste, use Ctrl + Alt + V.  That opens Paste Special.  Under the Operation section, select Add, then OK and the totals appear!  Then each month, copy your current numbers and use Paste Special, Add to the year-to-date.  No formulas, and no problems!

Consolidate is more complex, but it can save you a boatload of time when you need to gather numbers from several different sources on multiple worksheets and then total them all in one sheet.  For example, each of your worksheets contain the sales figures for each of your company’s regions.  Each line item is a different product which not all the regions sell.  What a cumbersome process you would have to follow! First, to sort all the products on each sheet and then to create a formula to collect all the totals ensuring the data matches the products.

Consolidate is a HUGE easy button!  It will look for all the same products (even if they are NOT in sort order) and sum them on a new sheet (even if some of the worksheets don’t list the product).

What’s the catch?  There is a tiny bit of setup involved: make sure that all the worksheets have the same columns appearing in the same order with the same column names.  (Tip: if you receive the worksheets regularly and they are not set up the same, use a macro to restructure them.)

Then, create a new worksheet for the totals and put your cursor in cell A1. Under the Data tab, click Consolidate. In the Reference section, click the collapse button (it looks like a tiny worksheet).  Go to the first worksheet and select the data.  Back in the Consolidate dialog box, click Add.  Check the Use Labels in Top Row and Left Column options. Repeat for each of the worksheets.  Then click OK.  Once you have selected where the data is, Excel does all the math for you.  What a time saver!

It is smart to let Excel do your routine calculations so you can spend your time and brain power on more important things like finding that calculator!

]]> 0
Hidden Security Options in Excel Thu, 01 Dec 2016 15:58:50 +0000

If you have any experience in Excel, then you have probably found a few reasons to want to secure your workbooks.  Here’s an example of what happened to Mary.  She spent weeks making the perfect worksheet for her sales team to collect data about their sales.  Then, accidentally, one of the sales reps deleted five critical columns along with all the information the team had already captured.  Yikes! Imagine how you would feel if that happened.

Security is certainly important for protecting high-dollar sales information from those who might unknowingly employ the delete key.  Almost everyone who shares a workbook could benefit from some of Excel’s security options.  Many of them are hidden gems that most people don’t know about.

Let’s look at a few of them and explore why you might use them.

Windows Properties

If you have a team who needs to see a workbook, but you don’t want them to make any changes to it, a simple solution is in Windows File Explorer.  You can right-click on the workbook file name to open the Properties window.  You can set the attributes to either Read Only or Hidden.

READ ONLY – Read Only allows them to look but not touch!  It’s like when you have the workbook open on a shared drive, and no one else can make changes.

HIDDEN – The other option, Hidden, is very tricky!  It actually hides it in plain sight.  While it still resides in the same location, no one – not even YOU – can see it.  So how do you access it?  Save a link to the workbook BEFORE you hide it! Be careful though, if someone pinned the workbook in Excel, they will still have access to it after it’s hidden.

Save As Password Options

This is so hidden, most people never find it on their own.  Excel allows you to add a password to the workbook with multiple options: Password to Open, Password to Modify and Read-only Recommended.

Password to Open means no one – not even YOU – can open the workbook without the password!  It’s good to keep track of it somewhere – especially if you have a lot of passwords.

Password to Modify is great for a template that you have created for others to use.  The only way they can change it is to save it with a new name.

Read-only Recommended reminds you to make it read only when you save it.  So where is this hidden gem?  When you save a workbook, you will see a link called Options. Click it and the classic Save As opens with the word Tools next to the Save button.  Click the Tools dropdown to see General Options and that’s where you can set the password.

BONUS: By the way, in Save As you can also save it as a PDF which means no one can modify the workbook in Excel, but then you probably already knew that one!


Excel provides a variety of options to hide or show specific information. For example, you can hide or show columns or rows by right-clicking on them.  Unfortunately, it’s pretty obvious they are hidden when the header numbers or letters are out of sequence.  But this is useful when you want information to just be out of the way while you work on the neighboring cells.

Here’s the gem: Did you know you can also hide a worksheet!?  This is beneficial when you have data that will roll up into one worksheet that the boss needs, but you don’t want to confuse him with all the other worksheets that contain the raw data.  Right-click on each of the tabs to hide them!  To show them again, right click on the NON-HIDDEN tab, select Unhide, then choose which sheets to show from the list.

These are just a few of Excel’s hidden security gems.  Start using the ones that make sense for you to help protect your data.

]]> 0
Understanding the Project Management Process Tue, 29 Nov 2016 14:46:45 +0000 Successfully guiding projects from initiation to implementation requires a balance of knowledge, skills, tools and techniques [1]. One of the best ways to make sure a project has the best possible outcome is to understand the processes involved in effective management. Projects can be divided into six phases [2].


What is the project meant to do? This is the best time to explore the ramifications of the concept, the potential costs, who you’ll need on a team to make it happen and whether it will have sufficient support to be completed. You will need to identify potential benefits, recruit team members, catalog the resources you have and imagine what you want the results to look like.

For every suggested feature or result, the project manager should be willing to say “no” and keep a tight rein on the project scope. The more people who weigh in on an idea, the more likely it is that things will balloon out of control until the project has almost no chance of finishing on time or budget.


The initial stage provides big ideas and broad strokes. The second phase is about the nitty-gritty details:

  • Define exactly what each requirement actually means, what tasks will have to be completed and what resources will be needed.
  • Set expectations for all stakeholders. Make sure everyone knows what can be achieved and what the reasonable limitations will be.
  • Think about the technology tools and support that will be required and identify obstacles that might arise.
  • Ask the tough questions about workload, deadlines and team capabilities. Remember, when you go through the approval process your higher-ups may ask hard questions to decide whether you can move forward, so make sure you already know the answers.

The list of defined requirements that come from this phase will be your guide for the rest of the project, so try to be as accurate and exhaustive as you can. Once you have your requirements, it’s a bad idea to go back and change them, so make sure this is a list you can be satisfied with.


Now it’s time to take that list of requirements and create a solid plan. This is when models, simulations, charts and timelines are built to show the various tasks and how they come together in the end. If the project itself requires a design phase (such as in software development) it’s best not to start out favoring one design over another; let the team work together to come up with several options. As with the definition phase, once the design is chosen, changing to a different one later will waste a lot of work and resources. Make sure all stakeholders are aware of the design and timelines and can easily see where everyone is making progress on their parts of the project.


Once the project is near completion, it’s time to implement testing (if applicable) and create a launch/rollout plan. Small and relatively informal projects can occasionally skip a long development phase, but don’t overlook it entirely. Development is where the project manager learns what is actually needed for launch and spots potential problems. Unfortunately at this stage, many companies are pushing for a hard roll-out date and will pressure managers to skip the development phase. A smart project manager will hold out for as much testing and launch planning as possible to make sure the final results meet the project’s goals. This will save time and money in the follow-up phase.

Project Launch

This is the spit and polish phase. The project manager might be asked to sit with sales teams or clients to explain the new product or address concerns that came from testing. They will need to check with the team regularly to make sure the project will meet its deadlines. At times, difficult decisions might be needed, such as calling in outside help or removing requirements from the list. This can also be a time for planning the tasks in the follow-up phase.


Follow-up often doesn’t get enough attention. This phase includes providing proper documentation and training, and seeing to it that help desk or maintenance teams are prepared for issues that might pop up after release. Follow-up is the vital time to confirm your stakeholders are satisfied with your work. It’s also the job of an effective project manager to recognize and celebrate the hard work that your team put into it.

For every phase, remember to think, and think again, before you make a final decision. This might slow you down in advance, but if you pay attention to the details, they won’t come back to haunt you during the follow-up.


]]> 1
Project Management Techniques for Career Development Tue, 29 Nov 2016 14:46:07 +0000 As a project manager, you may have a stable team that stays with you from project to project, or you may have different team members for each project. Regardless of whether your team is permanent or temporary, as the project manager, you have both the opportunity and responsibility to engage in career development with your team members.

How do you engage in career development when the pressure is on to deliver new products and services? Often, team members are selected because they bring specific skills to the project. Engaging in career development may seem outside the realm of the project. Here are some easy steps to integrate career development into your project management practices:

    • Talk to team members about their interests and career direction. Even if someone reports to you only on this project, learning about their interests may reveal opportunities for future engagements. Showing interest in individuals also builds loyalty and connection, this supports project performance.


    • Provide stretch opportunities that align with project needs. Maybe you have a technical specialist who would like more experience in customer outreach or supervision. Use that interest and existing skill base to identify a task that is needed on the project. For example, the team member could coach or mentor another team member in a specific technology, or lead an outreach meeting about a specific part of the project with external stakeholders. This builds on the team member’s existing experience, allows growth into new areas and helps the project.


    • Give both positive and corrective feedback. In the speed of a project, project managers don’t always provide developmental feedback. Instead, feedback often focuses on a particular deliverable and what needs to be corrected. Developmental feedback focuses instead on the person. This form of feedback speaks to the issue at hand, but also broadens the feedback to be more generally applicable to the person’s work. For example, perhaps the quality of a report is poor. Instead of giving feedback about the report, explore the root causes that led to the poor quality. That way, you both develop the person, and get a better report next time.


  • Network. If you are a full-time project manager, your success and development depends on access to talented individuals both inside and outside your organization. Build a network of people that you can later draw upon — know their interests, skills and possible availability. As you take on new projects, consider who might be ready for the challenge you currently have and explore the possibility of engaging them. Even if it does not work out, your interest will be noticed. Next time, people may approach you — and it may be just the right fit at the right time. Also, keep in touch with the people you have worked with in the past — by tracking their development over time; you can support succession planning throughout the organization.

Career development and project management are not often spoken about in the same breath — however, project managers that take career development seriously can both up their game on projects, and build their bench for the future.

]]> 0