Protecting formulas from being over written

 

To protect your formulas from being overwritten or modified perform the following steps :

1. Select the cells that may be overwritten.
2. Right-click and choose Format Cells from the shortcut menu as shown in Image 1.

Image 1

3. In the Format Cells dialog box, click the Protection tab.
4. In the Protection tab, clear the Locked check box as shown in the Image 2.

Image 2

5. Click OK to close the Format Cells dialog box.
6. Choose Review➜Changes➜Protect Sheet to display the Protect Sheet dialog box, as
shown in Image 3.

Image 3

7. In the Protect Sheet dialog box, select the options that correspond to the actions to
allow, specify a password if desired, and then click OK.
8. Make a list of the passwords. Once the password is lost the worksheet can never be edited.
9.While unprotecting the sheet, it will ask for the password as shown in Image 4.

Image 4

( Click on the Image to enlarge it.)

How to Open a VBA Workbook

The Visual Basic for Applications (VBA) is a program within Excel that allows you to communicate with Excel. 

Open Excel and open a new workbook.

On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor.( as shown in the image below)

Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.

( click on the image to enlarge it )

Add all continuous numbers of a column or row.

 In a worksheet enter a formula that uses the SUM function (also known as AutoSum) to quickly total a series of values without spending time to manually enter each cell.

Image 1

In the cell where you want the total of the column, write "= sum ". Then click on the first number cell that you want to add ( as shown in Image 1), drag the cursor to the last cell of which you want the total and close it with a bracket ( as shown in Image 2). Press Enter.

Image 2

The sum is shown in the total column and if you want to look at the formula later, it is shown in the formula bar in the upper side of the screen ( refer to Image 3).                                                                              

( click on the images to enlarge it )                                                                                  

Defining a Named Range

 Advantage of Naming a Range

When there is need to deal with big datasheets and complex formulas, it could create many problems when you access data by specifying its cell location. Naming a range a clever way to remember the purpose of a formula, rather than using cell references.

For example in the image below, the formula =Sum(Energydrink_Totalsales) is much more convenient than =SUM(D3:D11).

Using named ranges is more convenient than typing the address or selecting the data each time you want to refer to them. Named ranges also provide an advantage in that if you expand the range, perhaps to include additional data, the pivot table report will automatically include the new data when it is refreshed.

Image 1

 How to Define a Range :

Select the cell or cell range that you want to name.On the Formulas tab, click Define Name in the Defined Names group.( as shown in the image above)

The New Name dialog box appears.

    In the Name text box, type the name of the range. Try to use something descriptive. Like for the example in image 1 the Named Range is Energydrink_Totalsales. 

Click OK.

Points to remember while creating a range name :
 a)  The first character must be a letter, an underscore, or a backslash.
b)  No spaces are allowed in a range name, use a underscore if needed to separate words.
c)   The range name should not be the same as a cell address. For example, you can't name a range A27 
       or GH40.   

How to use a Named Range

To use a named cell or range, click the down arrow in the Name box at the left end of the Formula bar. Select the range name you want to access, and Excel highlights the named cells.

Image 2 

OR
Select a range name in the Name box to quickly locate an area of a worksheet.

Image 3

OR


Use range names with the Go To dialog box. Press F5 to display the Go To dialog box, select the range name you want to jump to, and click OK.

Image 4

 You can insert range names into formulas just like they were normal cell references.


(Click on images to enlarge it )

Creating pivot table using Excel sheet

 Step By Step Procedure to create Pivot Tables:


1. Open the Excel worksheet that contains the table you want summarized by pivot table and select any cell in the table.(Ensure that the table has no blank rows or columns and that each column has a header.)

Image 1

 2.Select the Insert tab. Click the top portion of the button for creating Pivot Tables OR  if you click the arrow, click PivotTable in the drop-down menu.(First from left side of the worksheet)

Excel opens the Create PivotTable dialog box and selects all the table data, as indicated by a marquee around the cell range.If necessary, adjust the range in the Table/Range text box under the Select a Table or Range option button. ( Make sure that you do not select the Heading. In the example below it is  "Energy Drink Sale By Store)

Image 2 

Image 3

 3. Select the location for the pivot table. By default, Excel builds the pivot table on a new worksheet it adds to the workbook. If you want the pivot table to appear on the same worksheet, click the Existing Worksheet option button and then indicate the location of the first cell of the new table in the Location text box. Enter the address of a cell that is a few rows below the data. ( For ex: A20 in the worksheet shown above)

4. Excel  displays a "PivotTable Field List" task pane on the right side of the new worksheet . The PivotTable Field List task pane is divided into two areas.
    a)   The "Choose Fields to Add to Report"  list box with the names of all the fields in the source data for the pivot table.
    b)  An area divided into four drop zones (Report Filter, Column Labels, Row Labels, and Values) at the bottom.

Image 4

 5. To complete the pivot table, assign the fields in the PivotTable Field List task pane to the various parts of the table. You do this by dragging a field name from the Choose Fields to Add to Report list box and dropping it in one of the four areas below, called drop zones:

          a) Report Filter: This area contains the fields that enable you to page through the data summaries shown in the actual pivot table by filtering out sets of data — they act as the filters for the report. 

         b) Column Labels: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.

        c) Row Labels: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.

      d) Values: This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).

Continue to manipulate the pivot table as needed until the desired results appear.

Image 5

Image 6

For the example  in Image 5 i had added  " Total sales" in " Values" area . Here you can see that it is taking it as count of numbers.To change it to currency, click on the Drop Down arrow of the " Sum of Totals" in the "Values" area. Then Select "Value field Setting". Under  " Summarize Value field by " select the option "Sum". Then to change it into Dollars or any Currency, select " Number Format" in the extreme left of the window. Here you can change it into currency as shown in Image 7.

Image 7

Your Simple Pivot Table is ready as shown in Image 8 below. To Edit the drop down Zone , just click on any cell in the tables and it will appear again.

Image 8

( Click on the Images to Enlarge it )