Category: excel

Workbook Security in Excel 2010 ”; Previous Next Workbook Security We can apply security to the workbook by the concept of protection available in the Review Tab of ribbon. MS Excel”s protection-related features fall into three categories. Worksheet protection − Protecting a worksheet from being modified, or restricting the modifications to certain users. Workbook protection − Protecting a workbook from having sheets inserted or deleted, and also requiring the use of password to open the workbook. Protect Worksheet You may want to protect a worksheet for a variety of reasons. One reason is to prevent yourself or others from accidentally deleting the formulas or other critical data. A common scenario is to protect a worksheet, so that the data can be changed, but the formulas can’t be changed. To protect a worksheet, choose Review » Changes group » Protect Sheet. Excel displays the Protect Sheet dialog box. Note that providing a password is optional. If you enter a password, that password will be required to unprotect the worksheet. You can select various options in which the sheet should be protected. Suppose we checked Format Cells option then Excel will not allow to format cells. When somebody tries to format the cells, he or she will get the error as shown in the screenshot below. To unprotect a protected sheet, choose Review » Changes group » Unprotect Sheet. If the sheet was protected with a password, you’re prompted to enter that password. Protecting a Workbook Excel provides three ways to protect a workbook. Requires a password to open the workbook. Prevents the users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets. Prevents users from changing the size or position of windows. Requiring a Password to Open a Workbook Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook, must enter the password. To add a password to a workbook, follow these steps. Choose File » Info » Protect Workbook » Encrypt With Password. Excel displays the Encrypt Document dialog box. Type a password and click OK. Type the password again and click OK. Save the workbook. To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols. Protecting Workbook’s Structure and Windows To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook’s structure and windows. When a workbook’s structure and windows are protected, the user may not Add a sheet, Delete a sheet, Hide a sheet, unhide a sheet, etc., and may not be allowed to change the size or position of a workbook’s windows respectively. To protect a worksheet’s structure and windows, follow the below mentioned steps. Choose Review » Changes group » Protect Workbook to display the Protect Workbook dialog box. In the Protect Workbook dialog box, select the Structure check box and Windows check box. (Optional) Enter a password. Click OK. Print Page Previous Next Advertisements ”;

Cross Referencing in Excel 2010 ”; Previous Next Graphic Objects in MS Excel When you have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. This is where the Vlookup function comes into its own. VLOOKUP VlookUp searches for a value vertically down for the lookup table. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) has 4 parameters as below. lookup_value − It is the user input. This is the value that the function uses to search on. The table_array − It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need. Col_index_num − It is the column of data that contains the answer that you want. Range_lookup − It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value be formatted in ascending order. VLOOKUP Example Let”s look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth, and the second shows their favorite color. How do we build a list showing the person”s name, their date of birth and their favorite color? VLOOOKUP will help in this case. First of all, let us see data in both the sheets. This is data in the first sheet This is data in the second sheet Now for finding the respective favorite color for that person from another sheet we need to vlookup the data. First argument to the VLOOKUP is lookup value (In this case it is person name). Second argument is the table array, which is table in the second sheet from B2 to C11. Third argument to VLOOKUP is Column index num, which is the answer we are looking for. In this case, it is 2 the color column number is 2. The fourth argument is True returning partial match or false returning exact match. After applying VLOOKUP formula it will calculate the color and the results are displayed as below. As you can see in the above screen-shot that results of VLOOKUP has searched for color in the second sheet table. It has returned #N/A in case where match is not found. In this case, Andy”s data is not present in the second sheet so it returned #N/A. Print Page Previous Next Advertisements ”;

Freeze Panes in Excel 2010 ”; Previous Next Freezing Panes If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. MS Excel provides a handy solution to this problem with freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet. Using Freeze Panes Follow the steps mentioned below to freeze panes. Select the First row or First Column or the row Below, which you want to freeze, or Column right to area, which you want to freeze. Choose View Tab » Freeze Panes. Select the suitable option − Freeze Panes − To freeze area of cells. Freeze Top Row − To freeze first row of worksheet. Freeze First Column − To freeze first Column of worksheet. If you have selected Freeze top row you can see the first row appears at the top, after scrolling also. See the below screen-shot. Unfreeze Panes To unfreeze Panes, choose View Tab » Unfreeze Panes. Print Page Previous Next Advertisements ”;

Adjust Margins in Excel 2010 ”; Previous Next Margins Margins are the unprinted areas along the sides, top, and bottom of a printed page. All printed pages in MS Excel have the same margins. You can’t specify different margins for different pages. You can set margins by various ways as explained below. Choose Page Layout » Page Setup » Margins drop-down list, you can select Normal, Wide, Narrow, or the custom Setting. These options are also available when you choose File » Print. If none of these settings does the job, choose Custom Margins to display the Margins tab of the Page Setup dialog box, as shown below. Center on Page By default, Excel aligns the printed page at the top and left margins. If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab as shown in the above screenshot. Print Page Previous Next Advertisements ”;

Discuss Excel ”; Previous Next Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. The current versions at the time of writing this tutorial are 2010 for Microsoft Windows and 2011 for Mac OS X. Microsoft Excel is a spreadsheet tool capable of performing calculations, analyzing data and integrating information from different programs. By default, documents saved in Excel 2010 are saved with the .xlsx extension where as the file extension of the prior Excel versions is .xls. Print Page Previous Next Advertisements ”;

Data Tables in Excel 2010 ”; Previous Next Data Tables In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel. Data Table with Example Now, let us see data table concept with an example. Suppose you have the Price and quantity of many values. Also, you have the discount for that as third variable for calculating the Net Price. You can keep the Net Price value in the organized table format with the help of the data table. Your Price runs horizontally to the right while quantity runs vertically down. We are using a formula to calculate the Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for each quantity). Now, for creation of data table select the range of data table. Choose Data Tab » What-If analysis dropdown » Data table. It will give you dialogue asking for Input row and Input Column. Give the Input row as Price cell (In this case cell B3) and Input column as quantity cell (In this case cell B4). Please see the below screen-shot. Clicking OK will generate data table as shown in the below screen-shot. It will generate the table formula. You can change the price horizontally or quantity vertically to see the change in the Net Price. Print Page Previous Next Advertisements ”;

Printing Worksheets in Excel 2010 ”; Previous Next Quick Print If you want to print a copy of a worksheet with no layout adjustment, use the Quick Print option. There are two ways in which we can use this option. Choose File » Print (which displays the Print pane), and then click the Print button. Press Ctrl+P and then click the Print button (or press Enter). Adjusting Common Page Setup Settings You can adjust the print settings available in the Page setup dialogue in different ways as discussed below. Page setup options include Page orientation, Page Size, Page Margins, etc. The Print screen in Backstage View, displayed when you choose File » Print. The Page Layout tab of the Ribbon. Choosing Your Printer To switch to a different printer, choose File » Print and use the drop-down control in the Printer section to select any other installed printer. Specifying What You Want to Print Sometimes you may want to print only a part of the worksheet rather than the entire active area. Choose File » Print and use the controls in the Settings section to specify what to print. Active Sheets − Prints the active sheet or sheets that you selected. Entire Workbook − Prints the entire workbook, including chart sheets. Selection − Prints only the range that you selected before choosing File » Print. Print Page Previous Next Advertisements ”;

Adding Graphics in Excel 2010 ”; Previous Next Graphic Objects in MS Excel MS Excel supports various types of graphic objects like Shapes gallery, SmartArt, Text Box, and WordArt available on the Insert tab of the Ribbon.Graphics are available in the Insert Tab. See the screenshots below for various available graphics in MS Excel 2010. Insert Shape Choose Insert Tab » Shapes dropdown. Select the shape you want to insert. Click on shape to insert it. To edit the inserted shape just drag the shape with the mouse. Shape will adjust the shape. Insert Smart Art Choose Insert Tab » SmartArt. Clicking SmartArt will open the SmartArt dialogue as shown below in the screen-shot. Choose from the list of available smartArts. Click on SmartArt to Insert it in the worksheet. Edit the SmartArt as per your need. Insert Clip Art Choose Insert Tab » Clip Art. Clicking Clip Art will open the search box as shown in the below screen-shot. Choose from the list of available Clip Arts. Click on Clip Art to Insert it in the worksheet. Insert Word Art Choose Insert Tab » WordArt. Select the style of WordArt, which you like and click it to enter a text in it. Print Page Previous Next Advertisements ”;

Excel – Useful Resources ”; Previous Next The following resources contain additional information on Excel. Please use them to get more in-depth knowledge on this topic. Useful Video Courses Excel – A Step by Step Complete Course 56 Lectures 6 hours TELCOMA Global More Detail Excel Makro ve VBA Eğitimi-1 37 Lectures 6.5 hours Volkan Yurtseven More Detail Excel Pivot Tables – Crash Course Most Popular 18 Lectures 47 mins EdSolver Learning More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Excel Analytics – Data Analysis with Pivot-Tables and Charts 47 Lectures 2.5 hours Blink Data More Detail Learn microsoft excel course for data analysis Zero to hero 50 Lectures 2 hours ADITYA More Detail Print Page Previous Next Advertisements ”;

Using Macros in Excel 2010 ”; Previous Next Macros in MS Excel Macros enable you to automate almost any task that you can undertake in Excel 2010. By using macro recorder from View Tab » Macro Dropdown to record tasks that you perform routinely, you not only speed up the procedure considerably but you are assured that each step in a task is carried out the same way each and every time you perform a task. To view macros choose View Tab » Macro dropdown. Macro Options View tab contains a Macros command button to which a dropdown menu containing the following three options. View Macros − Opens the Macro dialog box where you can select a macro to run or edit. Record Macro − Opens the Record Macro dialog box where you define the settings for your new macro and then start the macro recorder; this is the same as clicking the Record Macro button on the Status bar. Use Relative References − Uses relative cell addresses when recording a macro, making the macro more versatile by enabling you to run it in areas of a worksheet other than the ones originally used in the macro’s recording. Creating Macros You can create macros in one of two ways − Use MS Excel’s macro recorder to record your actions as you undertake them in a worksheet. Enter the instructions that you want to be followed in a VBA code in the Visual Basic Editor. Now let”s create a simple macro that will automate the task of making cell content Bold and apply cell color. Choose View Tab » Macro dropdown. Click on Record Macro as below. Now Macro recording will start. Do the steps of action, which you want to perform repeatedly. Macro will record those steps. You can stop the macro recording once done with all steps. Edit Macro You can edit the created Macro at any time. Editing macro will take you to the VBA programming editor. Print Page Previous Next Advertisements ”;