Skip to main content

Posts

Showing posts with the label Excel office

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-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-7

Using Templates in Excel 2010 Using Templates in MS Excel Template is essentially a model that serves as the basis for something. An Excel template is a workbook that’s used to create other workbooks. Viewing Available Templates To view the Excel templates, choose  File » New  to display the available templates screen in Backstage View. You can select a template stored on your hard drive, or a template from Microsoft Office Online. If you choose a template from Microsoft Office Online, you must be connected to the Internet to download it. The Office Online Templates section contains a number of icons, which represents various categories of templates. Click an icon, and you’ll see the available templates. When you select a template thumbnail, you can see a preview in the right panel. On-line Templates These template data is available online at the Microsoft server. When you select the template and click on it, it will download the template data from Microsoft server and opens i...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-6

Using Themes in Excel 2010 Using Themes in MS Excel To help users create more professional-looking documents, MS Excel has incorporated a concept known as document themes. By using themes, it is easy to specify the colors, fonts, and a variety of graphic effects in a document. And best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook. Applying Themes Choose  Page layout Tab » Themes Dropdown . Note that this display is a live preview, that is, as you move your mouse over the Theme, it temporarily displays the theme effect. When you see a style you like, click it to apply the style to the selection. Creating Custom Theme in MS Excel We can create new custom Theme in Excel 2010. To create a new style, follow these steps − Click on the  save current theme option  under Theme in Page Layout Tab. This will save the current theme to office folder. You can browse the ...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-5

Using Styles in Excel 2010 Using Styles in MS Excel With MS Excel 2010  Named styles  make it very easy to apply a set of predefined formatting options to a cell or range. It saves time as well as make sure that look of the cells are consistent. A Style can consist of settings for up to six different attributes − Number format Font (type, size, and color) Alignment (vertical and horizontal) Borders Pattern Protection (locked and hidden) Now, let us see how styles are helpful. Suppose that you apply a particular style to some twenty cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 12 pt. rather than 14 pt. Rather than changing each cell, simply edit the style. All cells with that particular style change automatically. Applying Styles Choose  Home » Styles » Cell Styles . Note that this display is a live preview, that is, as you move your mouse over the style choices, the selected cell or range temporarily displays th...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-4

Data Validation in Excel 2010 Data Validation MS Excel data validation feature allows you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and 10. If the user makes an invalid entry, you can display a custom message as shown below. Validation Criteria To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box. Select the cell or range. Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs settings, Input Message and Error alert. Settings Tab Here you can set the type of validation you need. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice. Any Value  − Selecting this option removes any existing data validation. Whole Number  − The...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-3

Using Ranges in Excel 2010 Ranges in MS Excel A cell is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell B1 is the cell in the second column and the first row. A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon. Example of Ranges − C24  − A range that consists of a single cell. A1:B1  − Two cells that occupy one row and two columns. A1:A100  − 100 cells in column A. A1:D4  − 16 cells (four rows by four columns). Selecting Ranges You can select a range in several ways − Press the left mouse button and drag, highlighting the range. Then release the mouse button. If you drag to the end of the screen, the worksheet will scroll. Press the Shift key while you use the navigation keys to select a range. Press F8 and then move the...
2 comments
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-2

Data Sorting in Excel 2010 Sorting in MS Excel Sorting data in MS Excel rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort data by Amount from smallest to largest or largest to smallest. To Sort the data follow the steps mentioned below. Select the Column by which you want to sort data. Choose Data Tab » Sort Below dialog appears. If you want to sort data based on a selected column, Choose  Continue with the selection  or if you want sorting based on other columns, choose  Expand Selection . You can Sort based on the below Conditions. Values  − Alphabetically or numerically. Cell Color  − Based on Color of Cell. Font Color  − Based on Font color. Cell Icon  − Based on Cell Icon. Clicking Ok will sort the data. Sorting option is also available from the Home Tab. Choose Home Tab » Sort & Filter. You can see the same dialog to sort records. The b...
Post a Comment
Read more

Microsoft Excel-office ADVANCED OPERATIONS Tips and Tricks-1

Data Filtering in Excel 2010 Filters in MS Excel Filtering data in MS Excel refers to displaying only the rows that meet certain conditions. (The other rows gets hidden.) Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this. Follow the below mentioned steps to do this. Place a cursor on the Header Row. Choose  Data Tab » Filter  to set filter. Click the drop-down arrow in the Area Row Header and remove the check mark from Select All, which unselects everything. Then select the check mark for Size 36 which will filter the data and displays data of Shoe Size 36. Some of the row numbers are missing; these rows contain the filtered (hidden) data. There is drop-down arrow in the Area column now shows a different graphic — an icon that indicates the column is filtered. Using Multiple Filters You can filter the records by multiple conditions i.e. by multiple column values. Suppose after size 36 is filtered, you need to h...
Post a Comment
Read more

Microsoft Excel-office WORKING WITH FORMULA Tips and Tricks-5

Built-in Functions in Excel 2010 Built In Functions MS Excel has many built in functions, which we can use in our formula. To see all the functions by category, choose  Formulas Tab » Insert Function.  Then Insert function Dialog appears from which we can choose the function. Functions by Categories Let us see some of the built in functions in MS Excel. Text Functions LOWER  − Converts all characters in a supplied text string to lower case UPPER  − Converts all characters in a supplied text string to upper case TRIM  − Removes duplicate spaces, and spaces at the start and end of a text string CONCATENATE  − Joins together two or more text strings. LEFT  − Returns a specified number of characters from the start of a supplied text string. MID  − Returns a specified number of characters from the middle of a supplied text string RIGHT  − Returns a specified number of characters from the end of a supplied text string. LEN  − Returns the lengt...
Post a Comment
Read more