15 Most Basic Things of Excel – Every Excel-user Must Know (Part-3)
(10.) Merge, Wrap Text and AlignmentExcel displays long text of a cell, up to the cell having value within the text length. Very often it is noted that user merges the cells or writes across rows instead of resizing that column or wrapping it or merging cells. If the data title is having sub data titles then the main data title looks meaningful, by merging and placing it in center across those subtitle columns. Cell content can be aligned/merged horizontally or vertically or both. Be careful while merging cells as it keeps contents of only first cell of selected cells. Wrap Text wraps longer text to the second line considering column width.
(12.) Function and FormulaEverybody knows that excel is used to perform calculative works. Formula is an expression which performs calculations, accordingly they are expressed. Function is a predefined formula. Some tasks can’t be done using formula. E.g. finding maximum value from a group of values or getting month/weekday from date automatically. There are some functions to satisfy these types of requirements. So, functions do much more than a formula. A formula can have one or more functions in it. Each and every function has its own syntax. A formula/function must be started with an equal “=” operator, which simply means that the cell value is an output of a formula expressed into it. Each function has particular number of parameters to be passed, to get the required work done. Violating or omitting any parameter may return wrong result or an error. TODAY(), NOW() and PI() functions do not need any parameter. Some parameters are optional. A function with optional parameters needs no value for that parameter or considers default value as that parameter. COLUMN() and ROW() functions are having only one parameter which is optional. Any formula cannot use the cell address as reference, in which the formula is being written. Otherwise it creates ‘Cross Reference’ and as a result ‘0’ (zero) will be returned.
(13.) Various Pasting Options and Format PainterCut, copy and paste are the most basic functionality of each-n-every software. In excel, ‘Paste Special’ provides more pasting options in addition to normal paste operations. However, excel pastes copied data considering source format. Even though there are few more options which provide solution to the number of problems. E.g. pasting just formulas ignoring all other characteristics of that cell, pasting only values instead of formula, pasting column widths as same as source columns etc. Each option is named as it works. ‘Transpose’ changes dimensions of data from rows to columns or columns to rows. The ‘Format Painter’ performs the same operation, but only for formats only.
(14.) Data Validation and ControllingPrecaution is always better than cure. No part of the worksheet is intelligent. They accept whatever user enters. Very often various validations are needed according to those data columns. E.g. ‘Birthdate’ column must contain dates only. “Data Validation” provides the same functionality for handling wrong data entry and data type. It is very useful when a workbook is used by more than one user.
(15.) Errors and ReasonsVery often user gets stunned by receiving an unsolvable error. Errors are not for harassing users, but they are for intimating the user for controlling wrong data or actions. Every error has its own meaning. Error can be resolved only if reason and meaning of that error is understood. So, for ‘panic-less’ error handling, user has to study meaning of those errors and reasons. E.g. ‘#Div/0’ is known as ‘Divide by zero’ error. It occurs if the divisor is zero. So, user has to look into the formula and rectify the devisor part or control the devisor part using ‘IF()’ function. Similarly ‘Cross Reference’ also can be handled easily if user knows how and why it has happened? With this, we complete the series of 3 posts for explaining you the basic things about Excel, to make you conversant and proficient in this highly-used tool. Hope we have been able to provide you sufficient information on Excel’s “must-know” things. We are sure you will remember all these 15 points while using Excel and become an Excel expert gradually!
About Jayant Joshi
Jayant Joshi is working as an Data Automation Specialist at Silver Touch Technologies Limited. He has 16+ years experience in Data Research, Data Preparation, What-If Analysis, Automated Report creation and VBA Application. Currently he is developing software and utilities in MS-Excel using Visual Basic for Application (VBA) environment. He has also written a book on MS-Office named "XP Guide".