Skip to main content

Posts

Showing posts from March, 2021

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

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

Using Functions in Excel 2010 Functions in Formula Many formulas you create use available worksheet functions. These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult if you use only the operators. For example, you can use the LOG or SIN function to calculate the Logarithm or Sin ratio. You can’t do this complicated calculation by using the mathematical operators alone. Using Functions When you type = sign and then type any alphabet you will see the searched functions as below. Suppose you need to determine the largest value in a range. A formula can’t tell you the answer without using a function. We will use formula that uses the MAX function to return the largest value in the range B3:B8 as  =MAX(A1:D100) . Another example of functions. Suppose you want to find if the cell of month is greater than 1900 then we can give Bonus to Sales representative. The we can achieve it with writing formula with IF functions as =IF(B9...
Post a Comment
Read more

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

Formula Reference in Excel 2010 Cell References in Formulas Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects new value automatically. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas. When you use a cell (or range) reference in a formula, you can use three types of references − relative, absolute, and mixed references. Relative Cell References The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas. Absolute Cell References The row and column references do not change when you copy the formula...
Post a Comment
Read more

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

Copying Formulas in Excel 2010 Copying Formulas in MS Excel Copying formulas is one of the most common tasks that you do in a typical spreadsheet that relies primarily on formulas. When a formula uses cell references rather than constant values, Excel makes the task of copying an original formula to every place that requires a similar formula. Relative Cell Addresses MS Excel does it automatically adjusting the cell references in the original formula to suit the position of the copies that you make. It does this through a system known as  relative cell addresses,  where by the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position. Let us see this with the help of example. Suppose we want the sum of all the rows at last, then we will write a formula for first column i.e. B. We want sum of the rows from 3 to 8 in the 9 th  row. After writing formula in the 9 th  row, we...
Post a Comment
Read more

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

Creating Formulas in Excel 2010 Formulas in MS Excel Formulas are the Bread and butter of worksheet. Without formula, worksheet will be just simple tabular representation of data. A formula consists of special code, which is entered into a cell. It performs some calculations and returns a result, which is displayed in the cell. Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature. For example, you can quickly change the data in a worksheet and formulas works. Elements of Formulas A formula can consist of any of these elements − Mathematical operators, such as +(for addition) and *(for multiplication) Example − =A1+A2 Adds the values in cells A1 and A2. Values or text Example − =200*0.5 Multiplies 200 times 0.15. This formula uses only values, and it always returns the same result as 100. Cell references (includ...
Post a Comment
Read more

Microsoft Excel-office FORMATTING WORKSHEETS Tips and Tricks-8

  Conditional Format in Excel 2010 Conditional Formatting MS Excel 2010 Conditional Formatting feature enables you to format a range of values so that the values outside certain limits, are automatically formatted. Choose  Home Tab » Style group » Conditional Formatting dropdown . Various Conditional Formatting Options Highlight Cells Rules  − It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text, or dates, or that have values greater or less than a particular value, or that fall within a certain ranges of values. Suppose you want to find cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To. After Clicking ok, the cells with value zero are marked as red. Top/Bottom Rules  − It opens a continuation menu with various options for defining the formatting rules that highlight t...
1 comment
Read more

Microsoft Excel-office FORMATTING WORKSHEETS Tips and Tricks-7

  Freeze Panes in Excel 2010 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 . The beginner to advan...
Post a Comment
Read more

Microsoft Excel-office FORMATTING WORKSHEETS Tips and Tricks-6

  Set Background in Excel 2010 Background Image Unfortunately, you cannot have a background image on your printouts. You may have noticed the  Page Layout » Page Setup » Background command. This button displays a dialogue box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed. Alternative to Placing Background You can insert a Shape, WordArt, or a picture on your worksheet and then adjust its transparency. Then copy the image to all printed pages. You can insert an object in a page header or footer. The beginner to advance and start up step-6 Thank you all very much  
Post a Comment
Read more