Skip to main content

Posts

Showing posts with the label Excel office 2010

Microsoft Access MS Access Basics Tips and Trick-7

Adding Data An Access database is not a file in the same sense as a Microsoft Office Word document or a Microsoft Office PowerPoint are. Instead, an Access database is a collection of objects like tables, forms, reports, queries etc. that must work together for a database to function properly. We have now created two tables with all of the fields and field properties necessary in our database. To view, change, insert, or delete data in a table within Access, you can use the table’s Datasheet View. A datasheet is a simple way to look at your data in rows and columns without any special formatting. Whenever you create a new web table, Access automatically creates two views that you can start using immediately for data entry. A table open in Datasheet View resembles an Excel worksheet, and you can type or paste data into one or more fields. You do not need to explicitly save your data. Access commits your changes to the table when you move the cursor to a new field in the same row, or whe...
Post a Comment
Read more

Microsoft Access MS Access Basics Tips and Trick-4

Create Database In this chapter, we will be covering the basic process of starting Access and creating a database. This chapter will also explain how to create a desktop database by using a template and how to build a database from scratch. To create a database from a template, we first need to open MS Access and you will see the following screen in which different Access database templates are displayed. To view the all the possible databases, you can scroll down or you can also use the search box. Let us enter project in the search box and press Enter. You will see the database templates related to project management. Select the first template. You will see more information related to this template. After selecting a template related to your requirements, enter a name in the  File name  field and you can also specify another location for your file if you want. Now, press the Create option. Access will download that database template and open a new blank database as shown in ...
Post a Comment
Read more

Microsoft Access MS Access Basics Tips and Trick-3

Objects MS Access uses “objects" to help the user list and organize information, as well as prepare specially designed reports. When you create a database, Access offers you Tables, Queries, Forms, Reports, Macros, and Modules. Databases in Access are composed of many objects but the following are the major objects:- Tables Queries Forms Reports Together, these objects allow you to enter, store, analyze, and compile your data. Here is a summary of the major objects in an Access database; Table Table is an object that is used to define and store data. When you create a new table, Access asks you to define fields which is also known as column headings. Each field must have a unique name, and data type. Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc. You can define a primary ke...
Post a Comment
Read more

Microsoft Access MS Access Basics Tips and Trick-2

RDBMS Microsoft Access has the look and feel of other Microsoft Office products as far as its layout and navigational aspects are concerned, but MS Access is a database and, more specifically, a relational database. Before MS Access 2007, the file extension was  *.mdb , but in MS Access 2007 the extension has been changed to  *.accdb  extension. Early versions of Access cannot read accdb extensions but MS Access 2007 and later versions can read and change earlier versions of Access. An Access desktop database (.accdb or .mdb) is a fully functional RDBMS. It provides all the data definition, data manipulation, and data control features that you need to manage large volumes of data. You can use an Access desktop database (.accdb or .mdb) either as a standalone RDBMS on a single workstation or in a shared client/server mode across a network. A desktop database can also act as the data source for data displayed on webpages on your company intranet. When you build an applicati...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-19

Keyboard Shortcuts in Excel 2010 MS Excel Keyboard Short-cuts MS Excel offers many keyboard short-cuts. If you are familiar with windows operating system, you should be aware of most of them. Below is the list of all the major shortcut keys in Microsoft Excel. Ctrl + A  − Selects all contents of the worksheet. Ctrl + B  − Bold highlighted selection. Ctrl + I  − Italicizes the highlighted selection. Ctrl + K  − Inserts link. Ctrl + U  − Underlines the highlighted selection. Ctrl + 1  − Changes the format of selected cells. Ctrl + 5  − Strikethrough the highlighted selection. Ctrl + P  − Brings up the print dialog box to begin printing. Ctrl + Z  − Undo last action. Ctrl + F3  − Opens Excel Name Manager. Ctrl + F9  − Minimizes the current window. Ctrl + F10  − Maximize currently selected window. Ctrl + F6  − Switches between open workbooks or windows. Ctrl + Page up  − Moves between Excel work sheets in the same Excel d...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-18

Pivot Charts Excel 2010 Pivot Charts A pivot chart is a graphical representation of a data summary, displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table. All Excel charting features are available in a pivot chart. Pivot charts are available under  Insert tab » PivotTable dropdown » PivotChart . Pivot Chart Example Now, let us see Pivot table with the help of an example. Suppose you have huge data of voters and you want to see the summarized view of the data of voter Information per party in the form of charts, then you can use the Pivot chart for it. Choose  Insert tab » Pivot Chart  to insert the pivot table. MS Excel selects the data of the table. You can select the pivot chart location as an existing sheet or a new sheet. Pivot chart depends on automatically created pivot table by the MS Excel. You can generate the...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-17

Simple Charts in Excel 2010 Charts A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral part of spreadsheets. Charts generated by early spreadsheet products were quite crude, but thy have improved significantly over the years. Excel provides you with the tools to create a wide variety of highly customizable charts. Displaying data in a well-conceived chart can make your numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. Types of Charts There are various chart types available in MS Excel as shown in the below screen-shot. Column  − Column chart shows data changes over a period of time or illustrates comparisons among items. Bar  − A bar chart illustrates comparisons among individual items. Pie  − A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always shows...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-16

Pivot Tables in Excel 2010 Pivot Tables A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. Pivot tables are very powerful tool for summarized analysis of the data. Pivot tables are available under  Insert tab » PivotTable dropdown » PivotTable . Pivot Table Example Now, let us see Pivot table with the help of example. Suppose you have huge data of voters and you want to see the summarized data of voter Information per party, then you can use the Pivot table for it. Choose  Insert tab » Pivot Table  to insert pivot table. MS Excel selects the data of the table. You can select the pivot table location as existing sheet or new sheet. This will generate the Pivot table pane as shown below. You have various options available in the Pivot table p...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-15

Data Tables in Excel 2010 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 ...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-14

Workbook Security in Excel 2010 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 passw...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-13

Translate Worksheet in Excel 2010 Translate Worksheet You can translate the text written in a different language, such as phrases or paragraphs, individual words (by using the Mini Translator), or translate your whole file with MS Excel 2010. Translation is available in the review tab of the ribbon in MS Excel 2010. You can quickly translate cell into different language with this option. Performing Translation Step By Step Select the content, which you want to translate to a different language. Choose  review tab » translation . It will open the pane from which you can select the language to which you need to translate. You need to have an internet connection for performing translation. It will translate using the Microsoft Translator. Click on Insert to apply translation changes.   The beginner to advance and start up step-13 Thank you all very much
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-12

Email Workbooks MS Excel 2010 Email Workbook MS Excel allows you to email the workbook very easily. To email the workbook to anyone, follow the below mentioned steps. Choose  File » Save and Send . It basically saves the document first and then the emails. Click on Send using E-mail if your email system is configured. MS Outlook will open with the file as attachment in the New Email Window. You can send mail this workbook to anyone with valid email address. The beginner to advance and start up step-12 Thank you all very much
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-11

Printing Worksheets in Excel 2010 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 en...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-10

Cross Referencing in Excel 2010 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_index6_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_index6_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 ...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-9

Adding Graphics in Excel 2010 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 ...
Post a Comment
Read more