Payroll calculation in Excel

In this Excel tutorial wage system employees are given incentive for increased productivity. In this system different tiers slabs or scales of output are defined and progressive slab has increased wage rate.

Calculating wages in this system is not simple as it was in earlier cases. Because in this situation first we have to identify the number of units employee has identified. Then we have to look up for the slab in which that output falls and take the rate of that slab and apply it to calculate wage.
Excel have functions or formula that do exactly the same and are called LOOKUP functions. We have different variants of these but the one I will be using is VLOOKUP.

How Excel VLOOKUP works – very briefly
Syntax:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup value: the value that you want to look up in the data

Table array: in simple words its the rage of data in which you will be finding the look up value. Remember the first column of range selected will be used for look up purposes

Column index number: the number of column where the target information is.

Range lookup: this is an optional field. So I am skipping the discussion on this just for now.

Steps of working:

1. Take the value as mentioned as lookup value
2. Look for this value in the first column of the range specified as table array.
3. And once the value is found excel will fetch the target information on the same row in the column which is identified by column index number.

Differential wage payments – Case 1

Step 1: Open worksheet named: DPW – S1
In this situation we have slabs as follows:



Step 2: Right click on any cell in Rate column. From the menu click “insert”The information of slabs is not in the form that we can use in the formula. We need only numbers. For this purpose we will add another column between Slab and Rate column named Class. To add column in between follow these steps:

Step 3: An insert dialogue box will appear. From the dialogue box select “Entire column” and click OK.
New column will be added between Slabs and column. Name the column “Class” and type 0, 100, 200…., 1000 in the column as shown below:




Observe the slabs. In this data 1-99 is one slab 100-199 is another slab. But how do excel knows this? Well the formula we are about to use understands this automatically as it is programmed like this. If employee has produced 95 units. It will go to 100 and see its on the next level so it will revert back to previous rate. So automatically it will stay within 1-99 range for 3 dollars rate.

Step 4: With the data ready to be used, we have employee information in with their names and units produced put the following formula in cell G5:
=F5*VLOOKUP(F5,B5:C15,2)

Press enter and wage of first employee is done. Drag the fill handler down or double click to process wages for the rest of employees as well.

In this formula B19 contains the value of units produced which we know before hand multiplied with the  appropriate rate for which we found using VLOOKUP function. And this solves our case.

Steps to enter above formula:

Step 1: Select cell and press = button. This will put excel in edit mode.

Step 2: Write B19 or select the value of units produced by first employee.

Step 3: Press and hold Shift button and hit 8 on the keyboard this will put asterisk  *  in the formula that works as multiply in mathematics.

Step 4: Type vlookup. Notice when you were typing the helper tag appears just under the field where you type a smart tip appears. You can make the selection of right function using directional arrows. Once VLOOKUP highlighted press TAB key on the keyboard.

Step 5: Select the same cell again i.e. units produced as you will use units produced to look up for the slab and hit comma button. Mostly found just at the left to enter key.

Step 6: Select the data in Class and Rate column with mouse. Lock the range i.e. make it absolute by cursor in or with the range and press F4 function key one time. This will put $ sign with the column and row references. Once done hit comma button on the keyboard.

Step 7: The target information is in the second column of selected range so press 2 key on the keyboard.

Step 8: Press and hold shift key and press 0 (zero) key to put closing parenthesis and hit Enter key.
To best practice all the above steps use the following worksheet. Few steps have already been done for you:

Payroll in Excel 2013

 Differential piece work wage payment – Case 2
Make sure the worksheet named: DPW – S2

In previous example our scale was set like 0-99, 100-199 and so on. Meaning everyone hundredth value was in the next slab. But what if we want the data to be like; 0-100, 101-200 and so on. In other words instead of 100 and above, 200 and above, 300 and above we want Upto 100, Upto 200, Upto 300 etc. This changes the scenario a little bit as previously 100, 200, 300 were in next slabs but in this case they are in the same slab.

To incorporate this we only have to change our range a little bit and rest of the steps are same as above. Instead of having classes as 0, 100, 200 and so on. Now the classes will be 0, 101, 201, 301 and so on. This way for 0-100 applicable rate will be 3, 101-200 applicable rate will be 5 and so on.

Rest of the steps for calculation are same as above.
You can practice this method with the following live worksheet. Few steps have been done for you already.

Payroll tutorial in Excel

Read more »

The best things of Office 365

The new Microsoft Office: As with the previous versions, you can get Word, Excel, PowerPoint and more as a locally installed suite of applications or as Office 365, a cloud-based subscription. However, choosing between Office 2013 desktop software and the new Office 365 is a dramatically different decision than in the past.
This time, there is virtually no decision to make. Comparing Office 2013 to Office 365 is an exercise in semantics; Microsoft has significantly stacked the deck to favor one over the other.

Microsoft has given Office 365 a clear advantage over Office 2013.
There is a clear distinction between the two options. Office 2013 describes only the desktop applications. By contrast, Office 365 is a Web-based platform that pairs the Office applications with cloud storage. In the past, though, the Office 365 versions of the software had limited features and capabilities compared to the full desktop versions, and if you didn’t have an Internet connection you didn’t have Office.

With the new versions of the productivity suite, though, Office 2013 vs. Office 365 is a smoke-and-mirrors debate. Office 2013 is more expensive than Office 365, and the license is only good for one machine. If you only need the core applications (Word, Excel, PowerPoint, and OneNote) you can get Office 2013 Home & Student for $140. Throw in Outlook, and you get Office 2013 Home & Business for $220. Office 2013 Pro adds Access and Publisher, all for $400.

Office 365 comes in two flavors: Office 365 Home Premium for $100 per year and Office 365 Small Business Premium for $150 per year. Both come with the full Office 2013 Pro software for your PC, but there are key differences. Up to five people can use Office 365 Home Premium on up to five devices, with each user getting an Office experience customized to their own Microsoft ID.

Office 365 Small Business Premium also comes with five licenses, but billed per user per year. Each user can install and use Office on up to five PCs, but the licenses can’t be shared with other users. Office 365 Small Business Premium also includes a managed Microsoft Back Office environment including Exchange, SharePoint, and Lync.

Office 365 includes five licenses for Office 2013 Pro.

Subject on how many computers and devices you want to install Office 2013 on through Office 365, and which version of Office 2013 you’re comparing to, it will take somewhere between one and a half to 20 years ($400 multiplied by five to install Office 2013 Pro on five machines comes to $2000—or 20 years of Office 365) for Office 2013 to become the more reasonable choice.

The only consequence that truly makes sense for Office 2013 is if you only need the software in Office 2013 Home & Student, and only on a single PC. In that case, you can spend the $140 and be done. Once you throw in a second PC, though, or if you need the additional tools like Outlook, Access, or Publisher, the math is heavily skewed in favor of the Office 365 subscription.

The beauty of Office 365 is that you get more than just Office 2013 for your money—it also comes with benefits that Office 2013 lacks. It comes with an additional 20GB of SkyDrive storage and 60 minutes per month of transnational Skype calls. Office 365 also has a new feature called Office On Demand that enables you to stream virtualized versions of the full desktop software to any Windows 7 or Windows 8 PC.

But, even for other stands or mobile devices there are Web-based versions of the Office applications, and as long as you store your files in SkyDrive you can access them seamlessly from virtually any Web-connected device. The world doesn’t end if your laptop is stolen or destroyed, and you can still edit a crucial client presentation even if you don’t have your PC with you.

Even if you only need the applications in Office 2013 Home & Student, it would cost $700 to put that software on five machines, and it would take seven years to break even on the cost of the Office 365 subscription. By that time, there will be a new version of Office (or two, maybe three). If you buy Office 2013 Home & Student, you’ll still have it in the year 2020. But, if you subscribe to Office 365 you will always have the most current version of Office available.

Microsoft has set things up so that the conclusion is already made. You are free to purchase Office 2013, but Office 365 has very clear advantages, and it makes more sense financially in almost every state.

Read more »

How to Add a Filter in Excel 2007-2010-2013

For example your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.

How to filter data:

In this example, we'll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout. In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail, and so on.

 Select the Data tab, then click the Filter command.

A drop-down arrow will appear in the header cell for each column.
Click the drop-down arrow for the column you wish to filter. In our example, we will filter column B to view only certain types of equipment.

 

 The Filter menu will appear. 
Uncheck the box next to Select All to quickly deselect all data.

Check the boxes next to the data you wish to filter, then click OK. In this example, we will check Laptop and Tablet to view only those types of equipment. 

The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and tablets are visible.

To apply multiple filters in Excel:

Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it down further to only show laptops and projectors that were checked out in August.
 

Click the drop-down arrow for the column you wish to filter. In this example, we will add a filter to column D to view information by date. 

The Filter menu will appear.
Check or uncheck the boxes depending on the data you wish to filter, then click OK. In our example, we'll uncheck everything except for August. 

 

The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and tablets that were checked out in August.  

How To clear a filter in Excel:

After applying a filter, you may want to remove, or clear, it from your worksheet so you'll be able to filter content in different ways.
Click the drop-down arrow for the filter you wish to clear. In our example, we'll clear the filter in column D. 

The Filter menu will appear.
Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out".

 

The filter will be cleared from the column. The previously hidden data will be displayed.  

To remove all filters from your worksheet, click the Filter command on the Data tab.

 

 

 

Read more »

PowerPoint 2013 Tutorial Applying Transitions

Transitions

There are three categories of unique transitions to choose from, all of which can be found on the Transitions tab:

• Subtle: These are the most basic types of transitions. They use simple animations to move between slides.
  
  Subtle transitions
• Exciting: These use more complex animations to transition between slides. While they're more visually interesting than Subtle transitions, adding too many can make your presentation look less professional. However, when used in moderation, they can add an nice touch between important slides.
  
  Exciting transitions
• Dynamic Content: If you're transitioning between two slides that use similar slide layouts, dynamic transitions will move only the placeholders, not the slides themselves. When used correctly, dynamic transitions can help to unify your slides and add a further level of polish to your presentation.
  
  Dynamic transitions

Applying a Transition:

1. Select the desired slide from the Slide Navigation pane. This is the slide that will appear after the transition.
   
   Selecting a slide
2. Click the Transitions tab, then locate the Transition to This Slide group. By default, None is applied to each slide.
3. Click the More drop-down arrow to display all transitions.
   Clicking the More drop-down arrow
4. Click a transition to apply it to the selected slide. This will automatically preview the transition.
   Selecting a transition

You can use the Apply To All command in the Timing group to apply the same transition to all slides in your presentation. Keep in mind that this will modify any other transitions you've applied.

Applying the same transition to all slides

 

Preview a Transition:

You can preview the transition for a selected slide at any time, using either of these two methods:

• Click the Preview command on the Transitions tab.
  Clicking the Preview command
• Click the Play Animations command in the Slide Navigation pane.
  Clicking the Play Animations command in the Slide Navigation pane

 

About Modifying Transitions

To Modify the Transition Effect:

You can quickly customize the look of a transition by changing its direction.

1. Select the slide with the transition you wish to modify.
2. Click the Effect Options command and choose the desired option. These options will vary depending on the selected transition.
   Modifying a transition effect
3. The transition will be modified and a preview of the transition will appear.
   

Some transitions do not allow you to modify the direction.

To Modify the Transition Duration:

1. Select the slide with the transition you wish to modify.
2. In the Duration field in the Timing group, enter the desired time for the transition. In this example, we'll increase the time to 2 seconds, or 02.00, to make the transition slower.
   Modifying the transition duration

To Add Sound:

1. Select the slide with the transition you wish to modify.
2. Click the Sound drop-down menu in the Timing group.
3. Click a sound to apply it to the selected slide, then preview the transition to hear the sound.
   Adding a sound to a transition

Sounds are best used in moderation. Applying a sound between every slide could become overwhelming or even annoying to an audience when presenting your slide show.

How to remove a Transition:

1. Select the slide with the transition you wish to remove.
2. Choose None from the Transition to This Slide group. The transition will be removed.
   
   Removing a transition from a slide

Now, to remove transitions from all slides, apply the None transition to a slide and then click the Apply to All command.

Read more »

PowerPoint 2013 Applying Theme

In PowerPoint 2010 or 2013 any theme is a predefined combination of colors, fonts, and effects. Different themes also use different slide layouts. You've already been using a theme, even if you didn't know it: the default Office theme. You can choose from a variety of new themes at any time, giving your entire presentation a consistent, professional look.

About Theme Components

Every PowerPoint theme, including the default Office theme, has its own theme elements. Those elements are:

• Theme Colors: There are ten theme colors, along with darker and lighter variations, available from every Color menu.
  Theme Colors
• Theme Fonts: There are two theme fonts available at the top of the Font menu under Theme Fonts.
  
  Theme Fonts
• Theme Effects: These affect the preset shape styles. You can find shape styles on the Format tab whenever you select a shape or SmartArt graphic.
  
  Theme Effects

Why we use Themes?

If you're using a theme, you'll probably find that your presentation looks pretty good. All of the colors will work well together, which means you won't have to spend as much time formatting your presentation. But there's another great reason to use theme elements: when you switch to a different theme, all of those elements will update to reflect the new theme. You can drastically change the look of your presentation in a few clicks.

The Wisp theme and the Integral theme

In our examples above, you can see the effect of applying different themes to the same slide—each theme uses its own fonts and colors. But you may have also noticed that the font and colors of the logo in the bottom-right remained unchanged: that's because they're Standard Colors and Fonts rather than theme elements. Colors and fonts will only update if you're using Theme Fonts or Theme Colors.

Themes and Slide Layouts

As you can see from the two different Title Slides above, themes can also change various slide layouts. Some themes, like the Wisp theme in the example below, even include additional layouts.

 

Wisp theme slide layouts and Integral theme slide layouts

If you use a unique slide layout, such as Quote with Caption or Name Card, and then switch to a theme that does not include that layout, it may give unexpected results.

Applying Themes

All themes included in PowerPoint are located in the Themes group on the Design tab. Themes can be applied or changed at any time.

To Apply a Theme:

1. Select the Design tab on the Ribbon, then locate the Themes group. Each image represents a theme.
2. Click the More drop-down arrow to see all of the available themes.
   Clicking the More drop-down arrow
3. Select the desired theme. 
4. Selecting a theme
5. The theme will be applied to the entire presentation. To apply a different theme, simply select it from the Design tab.
   
   The applied theme

Once you've applied a theme, you can also select a variant for that theme from the Variants group. Variants use different theme colors while preserving a theme's overall look. Some variants also use different textures, as in the example below.

Applying a theme variant in presentation

Read more »

How to Select the Right Chart Type in Excel 2010

Chart Types in Excel 2010

When we display your data visually in Excel 2010, choosing the right type of chart is just as important as deciding to use a chart at all. Different charts display the data in very different ways. Using the best chart type and format will help you display your data visually in the most meaningful way.

In the Insert Chart dialog box provides access to all the available chart types and subtypes.

Following is a description of the major chart types available in Excel, with some simple guidelines on when to use each type chart.

• Column charts: A column chart, unlike a bar chart to which it is often compared, emphasizes variation over a period of time. In a column chart, categories appear horizontally and values appear vertically, whereas in a bar chart, categories appear vertically. Variations include the cylinder, cone, and pyramid chart subtypes.
• Line charts: A line chart shows the relationship of the changes in the data over a period of time. Although similar to an area chart, which shows the relative importance of values, the line chart emphasizes trends rather than the amount of change.
• Pie charts: Pie charts contain just one chart data series. A pie chart shows the relationship of the parts to the whole. To emphasize the importance of one slice of the pie, choose one of the exploded 2-D or 3-D pie charts.
• Bar charts: A bar chart (horizontal bars) emphasizes the comparison between items at a fixed period of time. This chart type also includes cylinder, cone, and pyramid subtypes.
• Area charts: An area chart shows the relative importance of values over time. An area chart is similar to a line chart, but because the area between lines is filled in, the area chart emphasizes the magnitude of values more than the line chart does.
• XY (Scatter) charts: Scatter charts are useful for showing a correlation among the data points that may not be easy to see from data alone. An XY (Scatter) chart uses numeric values along both axes instead of values along the vertical axis and categories along the horizontal axis.
• Other Charts: All the other types of charts are lumped together on the drop-down gallery that appears when you click the Other Charts command button on the Ribbon's Insert tab:
  • Stock charts: Stock charts are used to plot stock quotes over a certain time period, such as a single business day or week. Stock charts show nearly any combination of a stock's highest and lowest values, its open and close values, and the volume of trade for that stock.
  • Surface charts: Surface charts plot trends in values across two dimensions in a continuous curve. In order to use a surface chart, you need at least two data series, both of which are numeric as with an XY (Scatter) chart.
  • Doughnut charts: A doughnut chart is similar to a pie chart except for its ability to display more than one data series (pie charts always graph just a single data series).
  • Bubble charts: Bubble charts compare sets of three values as kind of a combination of an XY (Scatter) chart with an Area chart. When you build a bubble chart, the size of each bubble represented on the x-y grid represents the third set of values being charted.
  • Radar charts: A radar chart shows changes in data relative both to a center point and to each other. This chart type is useful for making relative comparisons among items.

Read more »

How to Add a Filter in Excel 2007-2010

To Filtering is a convenient and reliable way to locate and manage spreadsheet data. We can filter data using the AutoFilter feature in Excel 2007-2010 to display only the data that meets specified criteria. Filtered data can be copied, manipulated, and printed without having to move it to a new spreadsheet. Using AutoFilter, we can filter data by selecting criteria from a list, by numerical conditions, or by color. Here's how to use the AutoFilter feature in Excel 2007-2010.

In Excel 2007 or Excel 2010, if you're using an Excel Table, it has built-in filtering features. If your data is not in an Excel Table, follow these steps to add an Excel 2007 AutoFilter.

1. Select a cell in the database.
2. On the Excel Ribbon, click the Data tab, and then click Filter.
   

A dropdown arrow appears at the right side of each column heading.

Filter One Column

With the Excel Table, or Excel 2007 AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns.
For example, to filter for a specific customer, follow these steps:

1. Click the drop down arrow at the right of the Customer heading
2. In the list of Customers, click Select All, to remove all the check marks
3. Click the check box for the customer that you want to filter for, such as MegaStore.
4. Click OK, to see the filtered list.

In the filtered worksheet, you'll see some changes:

• only the rows for the selected customer are visible -- the other customer rows have been hidden
• the row numbers for the filtered rows are shown in blue font.
• the drop down arrow in the Customer column has changed to a filter symbol, with a small arrow

Filter Two Columns

After you have filtered one column, you can refine the filtered results, by filtering two or more columns.
For example, after you filter for a specific customer, follow these steps to add a product filter:

1. Click the drop down arrow at the right of the Product heading.
   Note: When another column is filtered, you might not see the full list of products -- you'll only see the products for the filtered records.
2. In the list of Products, click Select All, to remove all the check marks
3. Click the check box, or multiple check boxes, for the product(s) that you want to filter for, such as Bananas and Grapes.
4. Click OK, to see the filtered list.

Clear an Excel 2007 or Excel 2010 AutoFilter

After you have applied one or more filters, you can clear a specific filter, or all the filters in the Excel Table.

Clear a Specific Filter

To clear a specific filter, and leave other filters turned on:

1. Click the drop down arrow in the column heading where you want to clear the filter
2. Click on Clear Filter From [column name]
   

Clear All Filters

Instead of clearing a specific filter, you can clear all the filters from the Excel Table, in one step:

1. Select a cell in the table.
2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Clear.
   Note: This will also clear the Sort options that you have applied.
   

Filter for Blank Cells

If there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list.
To show only the rows with blank cells in that column, remove the check marks from the other items in the list.

Filter for Highest and Lowest Values

If a column contains numbers, you can filter to find the highest or lowest numbers that column. In this example, you'll filter for the 5 lowest values in the Total column.

1. Click the drop down arrow in the column heading, click Number Filters, and then click Top 10.
   Note: If fewer than half of the values in the column are numbers, the Number Filters option will change to Text Filters

   
   

2. In the Top 10 AutoFilter window, select Bottom from the first drop down list.
3. In the second box, type 5, or use the arrows to change the number to 5
4. In the third drop down box, select Items, then click OK.

   
   

Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.

Filter for a Specific Date Range

If a column contains dates, you can filter to find a specific date or date range, or a dynamic date range. In this example, you'll filter for the orders received in January 2011.

1. Click the drop down arrow in the column heading, click Date Filters, and then click Between.

   
   

2. In the Custom AutoFilter dialog box, enter the starting date for the date range -- 01-Jan-2011 -- in the first date box
3. Enter the ending date for the date range -- 31-Jan-2011 -- in the second date box, and click OK
   

Filter for a Dynamic Date Range

If a column contains dates, instead of finding a specific date range, you can filter for a dynamic date range, such as Last Week, Next Month, or Tomorrow. In this example, you'll filter for the orders received in January 2011.

1. Click the drop down arrow in the column heading, click Date Filters, and then click Yesterday.

   
   

2. The filtered table shows only the orders that were received yesterday.

   
   

Update an Excel 2007 AutoFilter

When you open the workbook at a later date, the Excel 2007 AutoFilters don't refresh automatically. For example, if you used a dynamic date filter, such as Yesterday, the correct day's results won't show in the filtered table when you open the file the next day. (Note: You could create a macro to update the filter, and set it to run when the file opens.)
To see the updated filtered results, you can reapply the filter.

1. Select any cell in the filtered table
2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Reapply.
   
   

This will update any dynamic filters in the table, and will also revise the results for any new or changed data.

Create a Custom Filter

Some of the Excel 2007 AutoFilter options in the drop down lists end with three dots, such as Begins With...

These filters, as well as the Custom Filter option, open the Custom AutoFilter window. In there, you can enter one or two criteria, to create a customized filter.

To filter for one criterion:

1. From the first dropdown list, select an option, such as "begins with".
2. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where the customer name begins with "M".

To filter for two criteria:

1. From the first dropdown list, select an option, such as "begins with".
2. In the text box, type a value, then Click OK.
3. Select "And" or "Or" as the operator
4. From the first dropdown list, select an option, such as "ends with".
5. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where:

• the customer name begins with "M"
• OR the customer name ends with "Shop".

Read more »