Author Archives: bluemagma

Freeze or lock rows and columns in Excel

Leave a reply

http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP001217048.aspx

To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes (pane: A portion of the document window bounded by and separated from other portions by vertical or horizontal bars.).

When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet. For example, you might want to keep row and column labels visible as you scroll.

Worksheet window with row 1 frozen

A solid line indicates that row 1 is frozen to keep column labels in place when you scroll.

When you split panes, you create separate worksheet areas that you can scroll within, while rows or columns in the non-scrolled area remain visible.

What do you want to do?

Freeze panes to lock specific rows or columns

  1. On the worksheet, do one of the following:
    • To lock rows, select the row below the row or rows that you want to keep visible when you scroll.
    • To lock columns, select the column to the right of the column or columns that you want to keep visible when you scroll.
    • To lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible when you scroll.

ShowHow to select cells, ranges, rows, or columns

To select Do this
A single cell Click the cell, or press the arrow keys to move to the cell.
A range of cells Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet Click the Select All button.

Select All button

To select the entire worksheet, you can also press CTRL+A.

Note   If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.

You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note   You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column Click the row or column heading.

Worksheet showing row heading and column heading

Callout 1 Row heading
Callout 2 Column heading

You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note   If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.

Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner) Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
More or fewer cells than the active selection Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell (active cell: The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that you click becomes the new selection.

Tip   To cancel a selection of cells, click any cell on the worksheet.

  1. On the View tab, in the Window group, click the arrow below Freeze Panes.

Excel Ribbon Image

  1. Do one of the following:
    • To lock one row only, click Freeze Top Row.
    • To lock one column only, click Freeze First Column.
    • To lock more than one row or column, or to lock both rows and columns at the same time, click Freeze Panes.

Notes

  • When you freeze the top row, first column, or panes, the Freeze Panes option changes to Unfreeze Panes so that you can unlock any frozen rows or columns.
  • You can freeze rows at the top and columns on the left side of the worksheet only. You cannot freeze rows and columns in the middle of the worksheet.
  • The Freeze Panes command is not available when you are in cell editing mode or when a worksheet is protected. To cancel cell editing mode, press ENTER or ESC. For information about how to remove protection from a worksheet, see Protect worksheet or workbook elements.

Top of Page Top of Page

Split panes to lock rows or columns in separate worksheet areas

  1. To split panes, point to the split box at the top of the vertical scroll bar or at the right end of the horizontal scroll bar.

Split box

  1. When the pointer changes to a split pointer Split pointer or Resize pointer (double-headed arrow), drag the split box down or to the left to the position that you want.
  2. To remove the split, double-click any part of the split bar that divides the panes.

Note   You cannot split panes and freeze panes at the same time. When you freeze panes within a split pane, all rows above and columns to the left of the selection will be frozen and the split bar will be removed.

Insert Checkboxes In Excel 2010

Leave a reply

Excel provides very useful Form Controls which enable users to create a control & operate several things simultaneously. You can add Buttons, checkboxes, Labels, Combo-box, Scroll list etc. The real usage of form controls can be attained when you are dealing with colossal datasheet, and you need to invoke several functions and actions in desired order. These controls also abet you, when the data cells interconnect with each other. In this post we will be using Checkbox form control in a scenario where more than two ranges are related with each other.

Launch Excel 2010, and create a datasheet on which you want to link checkboxes with different actions. For instance, we have created an attendance sheet of students, containing fields, Name, andAttendance. We have also included another table in our datasheet that contains fields Total, Present,and Absent.

attendance sheet

To start off, we need to make Developer tab apparent on Excel window. In case you don’t find Developertab, go to File menu, click Options, and in left pane click Customize Ribbon, from right pane enableDeveloper check-box. Click OK to to see Developer tab on the ribbon.

developer tab

Now we will include checkboxes in the datasheet, in order to populate table entries with single click.  Navigate to Developer tab, from Insert options, click checkbox image present under Form Controls, as shown in the screenshot below.

developer

Upon Click, you will see plus sign pointer, click where you want to add checkbox.

checkbox

Select the text and remove it, and then right-click over it, click Format Control.

right click

The Control tab of Format Control dialog will open-up. By Cell link, select the location in the datasheet where you want to show the check/uncheck status of checkbox, which will be TRUE or FALSE respectively. Click Ok to continue.

check box options

Now we will move checkbox to the end of the Attendance cell, you will notice that the cell it is referring toH2 location, which will change the values TRUE/FALSE.

false 1

Upon enabling checkbox, the value in H2 location will automatically change. We will write formula inAttendance column at location C2, and that will check the value in H2 location,

=IF(H2=TRUE, “Present”, “Absent”)

The first parameter of the formula H2=TRUE, checks the value in H2 that if it is TRUE, keyword Presentwill be appear in the cell, and if it is False then Absent will appear in the cell.

Attendance present 1

Now follow the same procedure for including checkboxes with all the cells in Attendance field. As you can see in the screen shot below,  that we have created check boxes with the cells, and where the check box is enabled, the corresponding value at H2 column will change to TRUE, eventually through the formula evaluation the Present will appear in corresponding cell present in Attendance field.

complete table

Now we will start populating next table, here we have entered 10 in Total row (as we have 10 students).

next table 1

We will count the occurrence of keyword Present in the table Attendance column. So we will write formula as;

=COUNTIF(C2:C11, “Present”)

The result will be 9, as there is only one student Absent.

present studs `1

Now for checking how many students are absent, we will simple subtract number of students present, from total students. The formula goes like this

=($B$14-$B$15)

It will simply subtract value at B15 (students present) from value at B14 (Total), and yield the desired result.

attendance absent

You will also notice that on enabling/disabling checkbox, it will automatically update all the related info.

complete

12 Ideas for BAs to Deliver More Business Value in Less Time

Leave a reply

http://bit.ly/10ErtKx

From Enfocus Solutions…

Most every organization is under increasing pressure to deliver more, in less time, and at a lower cost. This goal of agile development has been very successful in many organizations.  Whether or not your organization has adopted agile, here are twelve innovative ideas you may want to consider to deliver more value in less time.

  1. Use Features: Break your project into features, prioritize the features, and work on the features that deliver the highest value first. This approach will provide many benefits including developing better requirements, delivering value faster, and eliminating low value functionality that is never really used.
  2. Measure, Measure, and Measure: It is important to start measuring your process if you don’t already do so. For starters, record the day when the idea was identified, when it was ready for development, when development started, when development ended, when it went into QA, and when it was deployed. Armed with those numbers you can calculate your lead-time from idea to going live. You can calculate the duration of your buffers: how long a feature is waiting before going into development, before going into QA and waiting for being released. Measuring the time from feature ideation to delivery is essential to shortening lead-times.
  3. Use Features to Promote Parallelism: Different sets of features often require different subject matter experts. For example, take implementing a new HR system; the SMEs for payroll processing are probably very different than the ones involved in recruiting or applicant tracking.  Separating the features into feature sets provides the opportunity to work on requirements for multiple features concurrently using multiple SME teams, thereby reducing time from ideation to implementation.
  4. Reusability: Reusing requirements can save significant amounts of time. For example, it is best to define nonfunctional requirements early as a separate feature and then reuse these across all other features. There are many other areas where requirements can be reused, thus increasing efficiency and reducing cycle time.
  5. Requirement Bundles: Features are very good for managing business value and for developing requirements. However, features may not be the best way to build the system.  Delivering requirements to development in bundles (i.e., groups of requirements organized by development iteration) provides opportunities for rapid development and for building and maintaining momentum.  As depicted below, managing features helps control the input to the backlog while managing bundles helps control the output for implementation.RequirementsBacklog
  6. Deliver Requirements Electronically, not via Paper: Many organizations still use Microsoft Word to create requirements and produce large paper requirement documents. Trying to maintain different versions of these documents and providing all related detail can be a real nightmare. Moving to electronic capture, tracking and delivery of requirements can save a great deal of time and allow all information about the requirement such as change history, conversations, visualizations and related business rules to be made available. Reproducing a new requirements document every time a small change is made to a requirement does not work well in today’s environment.
  7. Validate and Review Requirements Collaboratively: Requirements should be reviewed and validated as they are developed rather than waiting and validating all of them in a large paper requirements document.  Using a requirements collaboration tool such as Enfocus Requirements Suite™ (http://www.EnfocusSolutions.com) will shorten the cycle time for review and validation of requirements.  Catching requirements early can save time and product much better requirements and lead to a more satisfied user community.
  8. Deliver Requirements Just-in-Time and at the Right Level of Detail: The level of detail needed for requirements is a tough question the answer to which will vary based on number of issues such as:a. Is development outsourced?

    b. How much access will developers have to SMEs during development, if any?

    c. How much domain knowledge do the developers have?

    Developing requirements at the right level of detail is important.  Too much detail is expensive and can sometimes be confusing to developers.  Too little detail will result in developers making assumptions, being wrong, and under delivering on functionality, causing rework and delays or both, as well as leading to dissatisfied users. To minimize this risk, the level of detail should be agreed up front. Developers should be asked to review initial requirements to ensure that they are at the level of detail need for development.  Retrospectives should be conducted after each requirement bundle is delivered to development to determine if it is necessary to adjust the level of detail in requirements that is needed to support the project.

  9. Conduct Requirement Retrospectives: Requirements should be delivered incrementally to development in requirement bundles. After each requirement bundle has been delivered, a retrospective should be conducted to identify what worked well and what needed improvement. Retrospectives are used in agile development but seldom if ever used for requirements development. Continually making adjustments to requirements development processes helps significantly in producing excellent requirements at the right level of detail and eliminating waste from the process.
  10. Eliminate Unnecessary Functionality: As studies have shown, the 80/20 rule holds true for software development. With 20% of development you will satisfy 80% of your customer needs. It’s important to identify the 20% of your requirements that provide 80% of the value. Shorten the list of features or requirements to the 20% that provide most value and your lead-time for features will be dramatically reduced (in an optimal case by 5 times). Also keep in mind that studies have shown that as many as 64% of features are never or rarely used. Those features clog your development pipeline, increase lead-time for all other features, and aren’t used by your users!
  11. Frequent Releases: The biggest buffer is often the release buffer, so we’ll talk about that one before all other buffers. If you have a 6 month release cycle, most of your features will be developed, tested and begin providing value, but will need to wait several months to be released. You should consider shortening your release cycle to 2 or 3 months. Then, if you’re comfortable with this, shorten it to a monthly cycle.
  12. Eliminate or Shorten meetings: One big waste that prevents developers from developing code is meetings. Developers can get much more done when they concentrate on developing code by limiting the number of meetings they attend.  A collaborative requirements tool can help eliminate many meetings. Developers can request clarification and additional information via collaboration rather than attending meetings. Let your developers write code and work on customer value. Taking a look at Scrum will help. Scrum as an agile methodology has a pre-defined set of meetings, all with actionable and time boxed results. This minimizes the number of meetings conducted that seem to go on endlessly without clear results. 

 

 

 

Hacking Scrum for Personal Productivity

Leave a reply

http://www.projectmanagement.com/articles/276761/Hacking-Scrum-for-Personal-Productivity

Hacking the essence of Scrum Before proceeding, let’s outline those essential ideas from Scrum that are suited for personal productivity:

  • Break goals down – At center with Agile/Scrum is to break your goals down to manageable chunks. As we all know, Agile was a reaction to BUFR (Big Up Front Requirements), which advocates breaking your requirements down to manageable chunks and having something workable at the end of each iteration for a chunk worked on. Likewise, break your goals down to manageable chunks that can be accomplished in weeks (or weekly, as I like to do).
  • Sprint and validate – As I like to say, Scrum boils down to an agreement to not assume we’ll really know if the planning of the next Sprint/iteration will work as we thought, so we all agree to constantly inspect and adapt until a problem is found. When it’s found, we do something different or reboot the Sprint. So run your personal goals in a Sprint/iteration until the goal is achieved or modify your goal if a problem is found. Sometimes you may find the need to restart the whole Sprint if necessary. Also, remember to constantly inspect and adapt.
  • Daily reflection – The idea is to spend 15 minutes or less each morning to reflect on what you did yesterday, what you need to accomplish today and how to remove the impediments that are preventing you from achieving your daily goals. If you want, you may want a prioritized backlog of personal goals and realistically set achieving three to five goals per day at most. If you want, stand up during this daily reflection and even think about standing in front of a mirror dictating your agenda out loud to yourself–or in front of your spouse or pet to have it really stick (my dog is not only one of my best friends, but always listens to me with his tail wagging…great way to build that morning confidence!)

Scrum hacks applied As we can see, the essence of Scrum extrapolated gives us all the necessary ingredients to both effectively and efficiently manage our personal productivity. From quick personal goals such as losing five pounds by month’s end to large personal projects such as remodeling your home or paying off credit card debts, you can use all the practices with as little or more formality, collaboration and planning as necessary.

For personal productivity, I would recommend following one-week Sprints that are reviewed and planned for at the end of each week. This weekly planning and retrospective could occur on Sunday night or whatever day you think you can do it regularly. Keep a simple backlog of goals that you want to accomplish for each Sprint and consider ordering those based on criteria (such as those that directly contribute to you living a fulfilling and happy life). To ensure balance, keep in mind how each of them map to the great triangle of happiness: e.g., wealth, health and relationships. For example, setting a goal to pass the PMI Agile certification would allow you to get a higher paying position, which would fit under wealth. Exercising three times a week would fit health, and making sure to spend quality time each day with your family and friends would fit relationships.

But this is just one of many ways you can apply Scrum for your personal productivity efforts, and there are no hard and fast rules; to do so would go against the spirit of Agile. No matter how you go about it, keep the essence of Scrum outlined above always in the forefront. Break your goals down to manageable chunks with the end result of having something that can be empirically validated at the end of your Sprint to show for your efforts (e.g., if your goal is to lose two pounds at week’s end, then stepping on the scale and being two pounds or lighter is a working “demo” of your goal and validates). But if you find that you have too many tasty and temping snacks lying around in your refrigerator or cupboards, then you have to remove those impediments. Logging your diet and monitoring your weight daily may be needed to set the goals for the next day in your daily reflections.

Getting things done! Though what I’ve outlined is very simple and “common sense”, it seems we all still have many problems with it. All one has to do is peruse the Amazon booklist in the self-help category for things like time management and you will find a plethora of books by noted authors such as Brian Tracey and David Allen, who is is currently the undisputed champion in this genre with his infamous “Getting Things Done” (which I was once a devoted follower of). My enthusiasm waned when I discovered that I spent too much time creating elaborate and systematized lists of things to do (which is greatly advocated by the GTD methodology) and less time actually getting things done.

But having a background using Agile and applying its principles and practices in Scrum for my own productivity efforts boosted them since I stopped planning for them so much and consolidating the planning with the doing in more manageable chunks.

And yes, I’ve already contemplated using a Kanban board (like the one above) as a visual dashboard to see my weekly goals in progress as well as those pending; as was mentioned, things change and you always have to be inspecting and adapting to keep from getting stale. But the principles and practices of breaking goals to manageable chunks, iterating, validating and removing the impediments of your goals and reflecting regularly will always benefit your personal productivity no matter what “in fashion” method you name it.

With that I wish you a highly productive New Year!

Building a RACI Matrix

Leave a reply

http://businessanalyst.tarunchandel.com/2012/02/building-raci-matrix.html

 

Recently over the lunch table I was talking to one of my colleague who is struggling to improve some stagnated internal processes in his project. I offered to help him in my free time. Post lunch we went to his cubicle to discuss the problem in detail. As I got more involved I asked him if he has captured the details of the process in the current form (AS-IS) and if he has a vision of the future form of the process (TO-BE). He had done some impressive work and captured his understanding in Visio diagrams as well. It was very helpful to quickly understand the problem. I asked him if he has verified these diagrams with anyone else in the team. He denied saying he knew the processes really well and is capable of introducing the changes. I immediately raised my concern that to introduce a change he will need to involve the people who are concerned with the process. He asked me who all should he discuss this idea?

I put forward 4 simple questions in front of him to identify the people he needed to involve:

  1. Who is/will be doing this task?
  2. Whose head will roll if this goes wrong?
  3. Anyone who can tell me more about this task?
  4. Anyone whose work depends on this task?

Building a RACI Matrix, The Business Analysis Blog, Tarun Chandel

These questions will not only help him to identify the people to involve but also identify what role they will play. I left him with a diagram that roughly looked like the one I have posted here.

Building the RACI Matrix:

RACI Matrix, The Business Analysis Blog, Tarun Chandel

List down the tasks on the vertical axis and list the team members on the horizontal axis. Now go to each task one by one and start answering the above stated questions. Add more names as they are revealed during the exercise.

I met him few day later to know about the progress he has made. He was able to create a RACI matrix for his assignment but he was still struggling to make any progress. He was blaming the RACI matrix for creating the confusion and stalling the tasks in his team. I was amazed as in my experience RACI matrix always helped to clarify the doubts and create clear ownership of tasks. His RACI matrix was looking somewhat like the following figure (of course with different tasks):

RACI Matrix Gone Wrong, The Business Analysis Blog, Tarun Chandel

He had made some simple mistakes while creating the RACI matrix and they were not that difficult to rectify. While creating the RACI Matrix please ensure that you do a Horizontal and Vertical check.

Horizontal Check of the RACI Matrix (helps you to analyze each of the tasks):

  • Too many Rs: Activity needs more analysis and may have to broken into smaller tasks.
  • No Rs: Is this task worth pursuing? Who will do this task?
  • More than 1 A: Who is taking the ultimate decision.
  • No As: There must me an A.
  • Too many Cs and Is: Do we need to consult and inform each one of them?

Vertical Check of the RACI Matrix (helps you to analyze each of the team member or the role):

  • Too many Rs: Do we need to assign more people to reduce his/her workload?
  • Too many As: Do we need others to take few more decisions? Is there lack of delegation in the team?
  • No blanks: Does this person/role have to be involved in all the activities?
  • No As or Rs: Do we need this person in the team?

After checking his RACI matrix on the above parameters my friend was able to prune it to a working level. The important step that he took after that was to get it validated with all his team members as they need to commit to what has been put on the RACI matrix. The team also realized that RACI Matrix need not be completely right to start with and can evolve over the time.

RACI Matrix provides an effective tool to:

  1. Identify the roles and responsibilities
  2. Get the commitment of the team for tasks/activities.

POWER TIP: The idea is to make it a collaborative exercise as that will start the discussion in the right direction and generally results in higher commitment from the people involved. Remember to make people comfortable about the tasks and ensure that they don’t feel threatened to put their names against the tasks.

RACI in Agile:

Few days later as we were discussing how RACI Matrix has been an extremely helpful tool another colleague joined us for lunch. He works with the Agile methodology and he was not convinced that RACI is really needed as he never did that in his project.

One of the reasons I like agile is because it focuses on working effectively over documenting extensively. The term “RACI Matrix” is not there in the Agile projects but it is always there in principle. At the start of every Sprint while the team is committing to a Sprint Backlog team members volunteer for tasks or in other words taking the responsibility for that task. Product owner is the one who takes decision and is accountable throughout the agile project. Scrum Owner, SMEs, Clients are repeatedly consulted during an Agile project. Since everyone attends the daily standup meeting everyone is informed. Others who are not part of the team but need to know about the development of the team attend the Sprint Review (also sometimes called the Product Demo) meet at the end of each sprint to remain informed. Hence RACI is an integral part of any Agile project, but it is not referred to as RACI in Agile.
Just like so many things in Agile.