We can't think the life without computers now. We've computers in Office as well as at home. The major change this computer brought to us is ease in calculations.
All our bank statements, score card are now available in csv or in excel format and we check the results.
However many of us don't know real power of excel apart from just viewing the data. To use Excel in simple and smart way please find few tips.
- Format and Freeze: We can Format the rows and column ususally in AUTOFIT selection by FORMAT > COLUMN > AUTOFIT SELECTION and FORMAT > ROW > AUTOFIT SELECTION.
We lose the headings that are located at the top and the left side of the worksheet when we scroll large spreadsheets. To avoid this problem use WINDOWS > FREEZE PANES feature in Excel.
- HEADER and MACRO: We like to highlight HEADER with different font, type and/or with different fill color. This task becomes absolute necessity, once we open every new spreadsheet. We can record this operation in MACRO and using the CTRL key, we can do this operation more efficiently. TOOLS > MACRO > RECORD THE NEW MACRO. We can store this MACRO with CTRL key in MACRO list and refer wherever we need.
- SORT: To make the data in desired order, we need to sort it and sorting can be done by selecting the required data and DATA > SORT > SELECT FIELDS.
- DUPLICATES / UNIQUE ROWS: We can prepare the list of Unique values from the selected field as DATA > FILTER > ADVANCED FILETER > SELECT FIELD NAMES.
- UNIQUE FIELD / COMPARE ‘N’ FIELDS: To compare two fields we’ve functions like exact. However to compare set of N Fields with another set of same N fields, we don’t have any direct function. However we can create the Unique records by contacting N fields and using normal compare function we can compare the data. For e.g.: Unique field can be prepared from three fields as “= A1&B1&C1”.
- TRANSPOSE: In Copy and Paste option, we’ve PASTE SPECIAL > TRANSPOSE option which can be used to convert rows into column and vice versa. This option is beneficial in preparing header record.
- MATCH, FIND (VLOOKUP): If we want to match the pattern in another set of data we’ve powerful function called on VLOOKUP. The VLOOKUP function matches the input pattern in the range provided and display matched Index field. The HLOOKUP () is similar function and will find matching pattern in Horizontal row.
- Conditions (IF Statement): Conditional statements such as if provide way to build the complex Business Logic. IF( Condition, Value if True, Value of False)
- EXACT (): The exact function displays comparison result in binary format as TRUE or FALSE. The syntax is Exact( trext1, text2)
- HYPERLINK: This is easy option to move between intermediate sheets. RIGHT CLICK > HYPERLINK will help to add the desired path for the same.
These are 10 basic things which we use frequently in Excel in data management.
No comments:
Post a Comment