excel adv itt.pdf

UNDERSTANDING DATA ANALYSIS Predictive Analysis Prescriptive Analysis Text Analysis Text analysis, also known as text mining, is the process of recognizing patterns in text data using databases and data mining tools. Text Analys...

UNDERSTANDING DATA ANALYSIS Predictive Analysis Prescriptive Analysis Text Analysis Text analysis, also known as text mining, is the process of recognizing patterns in text data using databases and data mining tools. Text Analysis is about parsing texts to extract machine-readable facts from them. The main objective of Text Analysis is to create structured data out of free text content like emails, social media conversations, survey responses, online reviews, and more. You can think of it as slicing and dicing volumes of unstructured, heterogeneous documents into easier to manage data points in such a way to extract, examine, and ultimately unearth valuable insights. This is also very useful in Forensic Analysis. An example could be Word cloud tools where you see the result in terms of the number of occurrences and gaining insights into the sentiments. Statistical Analysis SAS defines Statistical analysis as “It’s the science of collecting, exploring, and presenting large amounts of data to discover underlying patterns and trends”. Statistical Analysis shows "What has happened?" by using historical data in the form of charts and Dashboards. Statistical Analysis could be based on the whole population or a sample thereof and includes the collection, analysis, interpretation, presentation, and modeling of data. It can be categorised into - Descriptive Analysis and Inferential Analysis. Descriptive Analysis (for describing the data) Descriptive statistical analysis as the name implies helps in describing the data. It gets the summary of data for example mean, deviation, percentages, or frequency in a way that meaningful information can be interpreted from it. Using descriptive analysis, we do not get to a conclusion however we understand and profile the data i.e. we get to know the quantitative description of the data. Descriptive statistics thus enable a meaningful way to present the data. A descriptive analysis is an important first step for conducting statistical analyses. Inferential Analysis (to generalize the population and draw inferences) Inferential Statistics as the name implies is used to generalize the population drawing inference from the samples. Where the sample is drawn from the population itself. It is necessary that the samples should be representative of the population and should not be biased. The process of achieving these kinds of samples is termed as sampling. Inferential Statistics comes from the fact that the sampling naturally incurs sampling errors and is thus not expected to perfectly represent the population. In this type of Analysis, different conclusions can be drawn from the same data by selecting different samples. Exploratory Data Analysis It is a good practice to understand data first. Exploratory data analysis as the name implies is the exploration of data set to look at patterns. Exploratory Data Analysis refers to the critical process of performing initial investigations on data to discover patterns, spot anomalies, test hypotheses and to check assumptions with the help of summary statistics and graphical representations. ADVANCED INFORMATION TECHNOLOGY 39  DATA ANALYSIS USING EXCEL AND CAAT TOOLS It is an analytical approach that focuses on identifying patterns in the data and figure out the unknown relationships. The purpose of Exploratory Data Analysis is to get check the missing data, find unknown relationships, and check hypotheses and assumptions. Diagnostic analysis Diagnostic Analysis as the name implies is about "Why did it happen?". It helps in determining why things are the way they are by finding the cause from the insight found in Statistical Analysis. This Analysis is useful to identify the root cause of the patterns in the data. If a new issue arises in a business process, then you can look into this Analysis to find similar patterns of that problem and probably locate the control weakness which caused the issue and could help in tackling similar issues. Predictive Analysis Predictive analysis as the name implies is used to predict future events. It is about “What might happen?” It is based upon the historical data and new data and behaviour, using the statistical algorithm and machine learning techniques determines the likelihood of future outcomes, trends. Businesses use predictive analytics to increase the competitive advantage and reduce the risk related to an unpredictable future. Assurance and forensics use it to compare Actuals with Predictions to look at anomalies and variances. An example is forecasting Prescriptive Analysis Prescriptive Analysis as the name implies tries to answer the question “What should be done”? It combines insight from all Analyses we discussed above to identify the best possible action for a situation. The objective of the prescriptive analysis is to provide advice that aims to find the optimal recommendation for a decision-making process. It takes inputs from Descriptive and Predictive analysis, Descriptive describes the data and predictive predicts the outcomes, prescriptive finds the best option. An example is Simulation, Graph analysis. STEPS IN DATA ANALYSIS There are a few steps involved in DATA ANALYSIS What data is required Collection of data Processing of Data Cleaning of Data Analysis Forming a Result Let us elaborate on the steps involved for a better understanding of the Data Analysis process What data is required The data required for analysis is based on specific demand or establishing the needs of an organisation. Based on the requirements of those directing the analysis process, the data necessary as inputs to the analysis are identified. This is one of the most important data analytics techniques as it will shape the very foundations of your process. Specific variables required may be specified and obtained. Data may be numerical or otherwise based on the different requirements of Analysts. 40 ADVANCED INFORMATION TECHNOLOGY  UNDERSTANDING DATA ANALYSIS Collection of data The collection of data is the process of gathering information that is demanded or identified as data required. The focus is on ensuring accurate and optimum collection of data from a verified source. The decision to be taken depends upon if, data gathered is accurate. You should decide on your most valuable data sources and start collecting your Data. Data collection sources may range from information in web pages to information derived specifically from database sources. This data may be unstructured and could have irrelevant information which need to be scrubbed, refined, cleaned, and processed to get the desired result. Processing of Data The collected data must be processed or structured in an organised form. This includes structuring the data as per requirement of various analytical tools. As an example, there could be a need that data be structured in rows and columns and may be inserted in a Data Model. Cleaning of Data The processed and organized data may be having errors, duplications, and unnecessary content. Trimming the informational fat is one of the most crucial methods of data analysis as it will allow you to focus your analytical efforts and squeeze every drop of value from the remaining ‘lean’ information. These errors can be prevented and corrected through Data Cleaning. Analysing Data This processed, clean and organised Data will be used for the analysis. You may use various data analysis techniques available to understand, interpret, and derive conclusions based on the requirements. By integrating the right technology for your statistical method data analysis, you can save ample time and effort while allowing you to enjoy the maximum value from your business’s most valuable insights. Various Statistical Data Models like Correlation, Multivariate Analysis is often wont to identify the relations among the data variables. These models that are descriptive of the data help simplify analysis and communicate results. Forming a Result The results of the data analysis would be reported in a format as needed by the users to support their decision making. The users might give their feedback which again could lead to more analysis. The data analysts can choose among various data visualization techniques, such as tables and charts, which may help in communicating the message easily and much conveniently and efficiently to the users. 1.3 BIG DATA Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. But it’s not the amount of data that’s important. It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves. Big Data refers to the large amounts of data which is pouring in from various data sources and has different formats. Even previously there was huge data which were being stored in databases, but because of the varied nature of this Data, the traditional relational database systems are incapable of handling this Data. Big Data is much more than a ADVANCED INFORMATION TECHNOLOGY 41  DATA ANALYSIS USING EXCEL AND CAAT TOOLS collection of datasets with different formats, it is an important asset which can be used to obtain enumerable benefits. Big data is data that contains greater variety arriving in increasing volumes and with ever-higher velocity. This is known as the three Vs. Put simply, big data is larger, more complex data sets, especially from new data sources. These data sets are so voluminous that traditional data processing software just can’t manage them. But these massive volumes of data can be used to address business problems you wouldn’t have been able to tackle before. The Three Vs of Big Data Volume The amount of data matters. With big data, you’ll have to process high volumes of low-density, unstructured data. This can be data of unknown value, such as Twitter data feeds, clickstreams on a webpage or a mobile app, or sensor-enabled equipment. For some organizations, this might be tens of terabytes of data. For others, it may be hundreds of petabytes. Velocity Velocity is the fast rate at which data is received and (perhaps) acted on. Normally, the highest velocity of data streams directly into memory versus being written to disk. Some internet-enabled smart products operate in real time or near real time and will require real-time evaluation and action. Variety Variety refers to the many types of data that are available. Traditional data types were structured and fit neatly in a relational database. With the rise of big data, data comes in new unstructured data types. Unstructured and semistructured data types, such as text, audio, and video, require additional preprocessing to derive meaning and support metadata. 1.4 DATA SCIENCE VS. DATA ANALYTICS Data is everywhere. We live in a data-driven world that revolves around facts and figures. In fact, the amount of digital data that exists is growing at a very tremendous rate, increasing manifold yearly. According to some researches, it results that in the coming years, about 2 megabytes of new information will be created every second for every human being on the planet, which makes it extremely important to know the basics of the field at least. After all, here is where our future lies. When we talk about data processing, Data Science vs Big Data vs Data Analytics are the terms that you might think of and there has always been confusion between them. Let’s first start with understanding what these concepts are. DATA SCIENCE It is the combination of statistical, mathematical, problem-solving tools available with us. It is the scientific ability to look at things differently. It also involves solving a problem in various ways to arrive at the solution and on the other hand, it involves designing and construct new processes for data modelling and production using various scientific tools.. Data science is a multidisciplinary field focused on finding actionable insights from large sets of raw and structured data. The field primarily fixates on unearthing answers to the things we don’t know we don’t know. Data science experts use several different techniques to obtain answers, incorporating computer science, predictive analytics, statistics, and machine learning to parse through massive datasets in an effort to establish solutions to problems that haven’t been thought of yet. 42 ADVANCED INFORMATION TECHNOLOGY  UNDERSTANDING DATA ANALYSIS DATA ANALYSIS It is defined as the science of examining raw data and processing such data to conclude into some information. It involves applying some defined process to derive information and conclude some results based on it. It is used by major industries to allow organizations and companies to make better decisions. The focus of Data Analytics lies in the process of deriving conclusions that are solely based on the knowledge of the researcher. Data analytics focuses on processing and performing statistical analysis on existing datasets. Analysts concentrate on creating methods to capture, process, and organize data to uncover actionable insights for current problems, and establishing the best way to present this data. More simply, the field of data and analytics is directed toward solving problems for questions we know we don’t know the answers to. More importantly, it’s based on producing results that can lead to immediate improvements. 1.5 TRADITIONAL APPROACH VS MODERN APPROACH TO DATA ANALYSIS The traditional approach to data analysis isolates analysis as a specialized reporting activity where data analysts are more working with “clean” or structured data since it is easier to compile, store, organise, and analyse. Traditional data analysis uses more of descriptive and exploratory analysis rather than predictive analysis to reveal performance results and discover patterns. In many cases, it is a long-drawn-out process involving processing cleaning and structuring data, many a time based on samples with tight assumptions about data, and by the time analysis is done it might be out of date and maybe not relevant. Results are often they are in the form of reports which might not answer all the questions. The traditional approach may also be using tools that also rely on many text-based commands to painstakingly analyse data and build charts. The modern approach is more about the ability to handle unstructured or “dirty” data and interpret and understand data at the speed of thought. This requires analytical engines to enable manage this heterogeneous and distributed data and provide results that can be optimized to solve a business problem. It uses more of Concepts of Machine Learning (ML), pattern recognition, and Big data techniques. With the help of these modern tools, you can find answers to problems as fast as you think of them while using massive and diverse data sets. With these Modern Tools, the insight-to-decision process is swift. From software installation to access to complex data sets and publishing interactive dashboards, the modern approach saves time at every step of the workflow. These Tools help create a visualisation of your data with simple drag and drop actions. Another aspect of the Modern approach is to use some framework for Data Analysis like Six Sigma Approach, BADIR, and others. 1.6 BADIR FRAMEWORK B.A.D.I.R is an acronym for the proprietary framework developed by the authors of Behind Every Good Decision, Priyanka Jain and Puneet Sharma. Data Science and Decision Science, progress through these 5 steps and it is believed that analytics fail when any of these steps are not followed or skipped altogether. This proprietary Data-to-Decisions framework is being widely used in organizations which involves the 5 different major steps defined below: ADVANCED INFORMATION TECHNOLOGY 43  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Business Questions – identify the business pain points Analysis Plan – plan and models are selected with other preparations Data Collection – collect data necessary for the analytics planned Derive Insights – discover insights from steps 1 to 3 Recommendations – provides suggestions/recommendations to the management STEP 1: Business Question In this we identify business pains and problems, which organization needs to resolve, we may start with asking questions like What, Who, Where, When, Why and How. Thus, you must understand what you are trying to solve. It is suggested to define a problem by taking root cause analysis. Understanding the context, the impacted segment, and potential reasons as understood by the business may provide a quicker path to resolving the problem. The most important question is “WHY” once you know Why then ask other questions for understanding the scope of the problem. This can not only be a business question, but also the Assurance or Forensic question, ultimately it means what is the objective of data analysis. STEP 2: Analysis Plan Now when you have defined the problems of the Business, they would be large in scope for implementation, so it is better to break them into smaller achievable parts in order to narrow the objectives to make sure goals are manageable. These goals should follow the S.M.A.R.T methodology – Specific, Measurable, Attainable, Relevant, and Time-bound. It’s important to only collect relevant data Some common analytics methodologies are used to help solve business problems, perhaps the most popular of all analytical models is SWOT – Strengths Weaknesses Opportunities and Threats. 44 ADVANCED INFORMATION TECHNOLOGY  UNDERSTANDING DATA ANALYSIS Finally, the analysis model and implementation plan will be concluded in this phase. Also, draft the project plan as it will tie together all of the information gathered and shall serve as the road map for the process execution. STEP 3: Data Collection This is the resultant step of the above two steps and is quite often described as the starting point of the process. Efforts put in the first 2 steps shall be the resultant of this step, which may result in less successful returns on the analysis if the first two steps of planning are not done properly. it is needed to collect relevant information (data) within the organizations from different systems or outside the organization such as open data in the market or from government bodies. It may also be noted that analysis does not work if our agenda is not set to the best of findings. The main parts of this step involve: Collection of the data following the data specification from the previous step. Cleaning data timely and validating it with our requirements to avoid the GIGO (garbage in, garbage out) syndrome, the data needs to be cleaned for usability and validated for accuracy. STEP 4: Insights This step is the execution portion of the process where data is being processed accordingly to the methodology prescribed for it. This process helps validate patterns in the data if there is a real problem and if there are unusual patterns in key variables. Then further additional testing is applied to prove or disprove hypotheses. Finally, the findings tested are presented to the user of 0such information. Moreover, it is important to make sure the flow and underlying processes on the insight discovery. STEP 5: Recommendations This may be the most crucial step as it is required to take action for the betterment of organisation to develop a positive impact on it. The main purpose of the process is to turn data into insights and then insights into actions. High-level recommendations will be presented to the top managers and values should be demonstrated throughout the presentation. Usually, the recommendations are done by presenting to an audience. Mainly following things need to be achieved Engage the audience by presenting a short and summarised set of recommendations. Be perceived as an effective business partner by presenting credible recommendations. Solve the business problem for which such a process is undertaken by deriving some actions that create impact. 1.7 CHALLENGE IN DATA ANALYTICS The amount of data being collected In this digitalized world, a large amount of data is produced every minute. The amount of data produced in every minute makes it challenging to store, manage, utilize, and analyse it. Even large business enterprises are struggling to find out the ways to make this huge amount of data usage. The amount of data produced is growing each day tremendously. Simply storing huge amounts of data will not be particularly useful, and that is why companies are exploring big data analysis tools that can help them deal with such massive data on a large scale. An organization may call for information on every incident and interaction that takes ADVANCED INFORMATION TECHNOLOGY 45  DATA ANALYSIS USING EXCEL AND CAAT TOOLS place daily, leaving analysts with thousands of interdependent and independent data sets. So, there is a need for a data system that automatically collects and organizes information Data Validation and Data Cleaning The second major Challenge is the Data quality problems, with these data is coming from across different platforms the issue is trying to clean data and data validation across these multiple, disjointed sources. Many surveys have found that many of the departments in a large business are not able to use data effectively due to redundancies and data complexity. According to an IBM study, poor data quality costs 3.1 trillion dollars per year in the US. Validating data is the process of identifying invalid values in your data, this would require knowledge of requirements for data. Data cleaning is a process that recognizes, corrects, deletes, and stores data from a database or any other data source. Data cleaning is different from data validation in that validation means data is rejected from the platform at entry and is performed at the same time whereas Data cleaning is performed on group of data at a later stage. Data quality affects the analysis, you would have heard the GIGO, Garbage in Garbage out, quality of analysis, insights will depend of the quality of data. Data cleaning plays a very important role in quality of Data. There are various types of inconsistency data issues for example: Duplicate data Conflicting Data Incomplete Data Invalid Data These issues could be caused due to improper synchronisation of data, software bugs in application., Poor application controls, information concealment by the user. In all the above cases data needs to be cleaned through various data cleaning tools that are available, but Data quality can also be improved with better data validations since data validations will correct data at source and will not allow erroneous data to enter the systems. Excel is a wonderful tool that can be used to validate and clean the data, in the next chapters this topic will be discussed in detail. 1.8 SUMMARY Many companies rely on the skills of data analysts to uncover actionable trends or sometimes, red flags; in this chapter, you learned the basics of Data Analysis. You have learned the differences in terms like Data Science, Big data, and Data analysis as well as the various steps in data analysis. You have also learnt about various types of data analysis answering various questions like What happened? Why did it happen? What might happen? What should be done? You learned the differences in the traditional and modern 46 ADVANCED INFORMATION TECHNOLOGY  UNDERSTANDING DATA ANALYSIS approaches to data analysis. BADIR is a proprietary Data-to-Decisions framework that enables Data analysis outcomes successful and meaningful. Finally, you understood the challenges involved in data analysis References Piyanka Jain, Puneet Sharma, ‘Behind Every Good Decision, McGraw-Hill Education,2014 Charles Wheelan, ‘Naked Statistics: Stripping the Dread From Data”, W. W. Norton & Company,2014 U Dinesh Kumar, “Business Analytics: The Science of Data”, Wiley, 2017 Anil Maheshwari, “Data Analytics Made Accessible”, Kindle, 2020 ADVANCED INFORMATION TECHNOLOGY 47  CHAPTER 2 EXCEL BASICS AND CUSTOMISATIONS LEARNING OBJECTIVES ◼ To understand basic Screen overview ◼ To understand the Quick Access Toolbar ◼ To understand Data Types with New rich Data Types ◼ To How to Customise Options in excel and Trust centre ◼ To understand the use of Go To Special Feature ◼ To understand the Benefits of Paste Special ◼ To manage Worksheets, rows, and Columns in Excel 2.1 INTRODUCTION Excel is the world's most generally utilized spreadsheet programming and part of the Microsoft Office suite. Other spreadsheet programs like Google Sheets, OpenOffice calc, etc. are available, however, Excel is by a wide margin the most well-known and has been the world norm for a long time as far as spreadsheets are concerned. No matter which career path you choose, you will likely need to use Excel to accomplish your daily professional work. Taking a straightforward view, Excel is an application that permits you to enter quantitative infor mation into an electronic spreadsheet to apply one or numerous numerical calculations. These calculations eventually convert that quantitative information into data. Using Excel, you can analyse large amounts of data and report those results in a meaningful way Much of the appeal of Excel is since it very versatile and evolving as per the requirements. Some of the major uses of Excel are: Calculations: Create Financials, tabulate Numbers, what-if analysis, and perform just about any type of financial analysis. Text manipulation: Clean data which is text. Creating Tables: Use the row-and-column layout to tabulate data efficiently. Creating charts: Create a wide variety of highly customizable charts. Accessing data from varied sources: Link or Import data from a wide variety of sources. Creating visualisations: Summarize a large amount of business information in a dashboard  EXCEL BASICS AND CUSTOMISATIONS Creating graphics and diagrams: Use Shapes and SmartArt to create professional-looking diagrams. Automating tasks: Using Macros, many repetitive tasks can be automated. 2.2 EXCEL WORKBOOK AND WORKSHEET The task you do within Excel is carried out in a workbook file. You may have numerous workbooks open which you require, and each one appears in a separate window. By default, Excel workbooks use an. xlsx file extension, though there are other extensions like.xlsm (Macro enabled workbooks) or.xlsb (Excel binary workbook) etc. A workbook can be thought of a book containing many sheets of paper and worksheet is akin to a single sheet of paper in that book. Thus, each workbook can have more than one worksheet, the worksheet is the name given to the large grid where you perform your calculations. Every worksheet has 16,384 columns (A - XFD) and 1,048,576 rows. This means that every worksheet contains 17,17,98,69,184 individual cells. This cannot be changed. The exact number of cells displayed at any one time depends on the size of the screen, screen resoluti on, and video driver. Each intersection of a row and a column defines a cell and this cell can be identified by its unique address. Each cell can contain a value, a formula, or text. The worksheet can also hold charts, images, and diagrams. Each worksheet in a workbook is accessible by clicking the tab at the bottom of the workbook window. Excel is a massive application with a multitude of features and hundreds of ribbon (menu) commands. It is very easy to get lost once you open Excel. So, one of the basic survival skills is to understand how to navigate Excel and access the features you are looking for. This Figure shows you some important parts of the screen: ADVANCED INFORMATION TECHNOLOGY 49  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.2.1 Parts of an Excel screen Broadly we can categorise into 5 parts Quick Access Toolbar: This is a place where all the important tools can be placed. Ribbon: This is an expanded menu. Formula bar: This is where any calculations or formulas you write will appear. Spreadsheet Grid: This is where all your numbers, data, charts & drawings will go. Status bar: This tells us what is going on with Excel at any time. 50 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2.2.1 PARTS OF THE EXCEL SCREEN Title bar This displays the name of the program and the name of the current workbook. It also holds the Quick Access toolbar (on the left) and some control buttons that you can use to modify the window (on the right). Quick Access Toolbar As the name suggests it is the place to keep the items that you not only need to access quickly but want to be immediately available regardless of which of the Ribbon's tabs you are working on. When you start Excel for the very first time, it has only a couple of icons (Save, Undo, Redo), you can, however, add any feature of Excel to the Quick Access Toolbar through adding more commands Tell Me This is a text field where you can enter words and phrases about what you want to do next and quickly get to feature you want to use or actions you want to perform. You can also use Tell Me to find help about what you are looking for or to use Smart Lookup to research or define the term you entered. Window Close button Click this button to close the active workbook window. Window Maximize/Restore button This button helps toggle to increase the workbook window’s size to fill the entire screen. Minimize button Click this button to minimize the workbook window. The Ribbon The ribbon is like an expanded menu. It is designed to help you quickly find the commands that you need to complete a task. Commands are organized in logical groups, which are collected under Tabs. Each tab relates to a type of activity, such as Home, Insert, Formulas, Page Layout & Data. To reduce clutter, some Tabs are context-sensitive, shown only when needed. For example, the Pivot Table tab is shown only when a PivotTable is selected. Tab list Use these commands to display a different Ribbon, like a menu. Collapse the Ribbon button Click this button to temporarily hide the Ribbon. And pin it to make the Ribbon remain visible. File Menu Opens the backstage, here you will find the basic commands such as new, open, save, print, export etc. Help button Click this button to display the Excel Help system window. ADVANCED INFORMATION TECHNOLOGY 51  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Active cell indicator: This dark outline indicates the currently active cell (one of the 17,179,869,184 cells on each worksheet). Each cell is represented with a Column and Row address like A1 to XFD1048576 Name box This box displays the active cell address or the name of the selected cell, range, or object. Formula Bar A place where you can enter or view formulas or text. Expand Formula Bar Button This button allows you to expand the formula bar. This is helpful when you have either a long formula or a large piece of text in a cell. Column letters Letters range from A to XFD — one for each of the 16,384 columns in the worksheet. You can click a column heading to select an entire column of cells or drag a column border to change its width. Row numbers Numbers range from 1 to 1,048,576 — one for each row in the worksheet. You can click a row number to select an entire row of cells Worksheet Navigation Tabs By default, every workbook starts with 1 sheet. A workbook can have any number of sheets, and each sheet has its name displayed in a sheet tab. Sheet tab scroll buttons Use these buttons to scroll the sheet tabs to display tabs that are not visible. You can also right-click to get a list of sheets. Insert Worksheet Button Click the Insert New Worksheet button to insert a new worksheet in your workbook. Horizontal/Vertical Scroll Allows you to scroll vertically/horizontally in the worksheet. Status bar This bar displays various messages, as well as the shows summary information about the range of cells selected. Right-click the status bar to change the information displayed. Macro recorder indicator Click to start recording a VBA macro. The icon changes while your actions are being recorded. Click again to stop recording. 52 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Normal View This is the “normal view” for working on a spreadsheet in Excel. Page Layout View View the document as it will appear on the printed page. Page Break Preview View a preview of where pages will break when the document is printed. Zoom Level Allows you to quickly zoom in or zoom out of the worksheet 2.2.2 NAVIGATING IN A WORKSHEET There are various ways to navigate the cells in a worksheet. Each worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). Column labeling is Column A to column Z followed by column AA to column AZ and so on until Column XFD. The cell address is the intersection of a row and a column, and each cell has a unique address made up of its column letter and row number. For example, the address of the upper-left cell is A1. The address of the cell at the lower right of a worksheet is XFD1048576. Figure 2.2.2 Cells A1 to XFD1048576 At any given time, one cell is the active cell. The active cell is the cell that accepts keyboard input, and its contents can be edited. You can identify the active cell by its darker border, as shown in Figure1.3. Its address appears in the Name box. Depending on the technique that you use to navigate through a workbook, you may or may not change the active cell when you navigate. ADVANCED INFORMATION TECHNOLOGY 53  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.2.3 Active Cell The active cell is the cell with the dark border — in this case, cell B2 You can use the navigational keys on your keyboard to move around a worksheet. The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.) Key Action Up arrow (↑) Moves the active cell up one row Down arrow (↓) Moves the active cell down one row Left arrow (←) or Shift+Tab Moves the active cell one column to the left Right arrow (→) Moves the active cell one column to the right PgUp Moves the active cell up one screen PgDn Moves the active cell down one screen Alt+PgDn Moves the active cell right one screen Alt+PgUp Moves the active cell left one screen Ctrl+Backspace Scrolls the screen so that the active cell is visible Using a mouse, you can change the active cell by just click another cell, and it becomes the active cell. If the cell that you want to activate is not visible in the workbook window, you can use the scrollbars to scroll the 54 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS window in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar’s scroll box. 2.2.3 EXCEL RIBBON Microsoft Excel ribbon is the row of tabs and icons at the top of the Excel window that allows you to quickly find, understand, and use commands for completing a certain task. Figure 2.2.4 Excel Ribbon The ribbon in Excel is made up of four basic components: tabs, groups, dialog launchers, and command buttons. RIBBON TABS contain multiple commands logically subdivided into groups. RIBBON GROUP is a set of closely related commands normally performed as part of a larger task. DIALOG LAUNCHER is a small arrow in the lower-right corner of a group that brings up more related commands. Dialog launchers appear in groups that contain more commands than available space. Figure 2.2.5 Dialog Launcher ADVANCED INFORMATION TECHNOLOGY 55  DATA ANALYSIS USING EXCEL AND CAAT TOOLS COMMAND BUTTON is the button you click to perform a particular action. It could be one of the following: Simple Button: You click, and it does what is required. An example is Cut, you click, and it is cut immediately. Figure 2.2.6 Simple Button Toggle Button: This kind of a button shows has two states if it is activated has a different colour, so when you click it you can toggle between activated not activated. An example is Wrap Text, if it is coloured you can tell that text wrap is activated and you can deactivate by clicking on the button again. Figure 2.2.7 Toggle Button Simple Drop Down: In this Ribbon could have a small drop-down arrow, which when clicked gives additional commands. An example is Insert, which when clicked gives more options. Figure 2.2.8 Drop Down Button 56 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Split Button: This is a combination of a button with a small drop-down arrow beside it, If you click on the button command gets executed, if you click on the dropdown gives additional commands. An example is Underline, which also has a dropdown beside it which when clicked gives more options. Figure 2.2.9 Split Button 2.2.4 RIBBON TABS The commands available in the Ribbon vary, depending upon which tab is selected. The Ribbon is arranged into groups of related commands. The appearance of the commands on the Ribbon varies, depending on the width of the Excel window, some of the commands may be grouped and need to be expanded if the width is too narrow. The standard Excel ribbon contains the following tabs, from left to right: File – allows you to jump into the backstage view that contains the essential file -related commands and Excel options. Home – contains the most frequently used commands such as copying and pasting, formatting, etc. Insert – is used for adding different objects in a worksheet such as images, charts, PivotTables, hyperlinks, Sparklines, headers, and footers. Draw –It lets you draw with a digital pen, mouse, or finger. This tab is available in Excel 2013 and later, but it is not visible by default. Page Layout – provides tools to manage the worksheet's appearance, both onscreen and printed. These tools control theme settings, gridlines, page margins, object aligning, and print area. Formulas – contains tools for inserting functions, defining names, and controlling the calculation options. Data – holds the commands for managing the worksheet data as well as connecting to ex ternal data. Review – allows you to check spelling, track changes, add comments and notes, protect worksheets, and workbooks. View – provides commands for switching between worksheet views, freezing panes, viewing, and arranging multiple windows. Help – only appears in Excel 2019 and Office 365. This tab provides quick access to the Help Task Pane and allows you to contact Microsoft support, send feedback, suggest a feature, and get quick access to training videos. Developer – provides access to advanced features such as VBA macros, ActiveX and Form controls, and XML commands. This tab is hidden by default and you must enable it first. ADVANCED INFORMATION TECHNOLOGY 57  DATA ANALYSIS USING EXCEL AND CAAT TOOLS 2.2.5 CONTEXTUAL RIBBON TABS In addition to the constant tabs described above, the Excel ribbon also has context-sensitive tabs, alias Tool Tabs, which show up only when you select a certain item such as a table, chart, shape, or picture. For example, if you select a Table, the Table Design tab will appear. Figure 2.2.10 Contextual ribbon 2.2.6 HIDING THE RIBBON IN EXCEL You can hide the Ribbon can either be hidden or made visible to toggle the Ribbon’s visibility, press Ctrl+F1 You can hide the ribbon completely by clicking the Ribbon Display Options button at the upper-right corner of the Excel window, and then clicking Auto-hide Ribbon. Ribbon Display Figure 2.2.11 Hide Ribbon 2.2.7 CUSTOMISING THE RIBBON You can personalise the ribbon according to your needs, so you know exactly where everything is located. Method 1: File > Options > Customize the Ribbon For this, you need to navigate to the Customize Ribbon window under Excel Options. 58 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Method 2: Right Click on Ribbon And the shortest path to it is to right-click on the ribbon and select Customize the Ribbon Figure 2.2.12 Customising Ribbon From Customise the Ribbon, you can add your tabs with any commands you choose, change the order of tabs and groups, show, hide, rename tabs, etc. What you can customise: The show, hide and rename tabs. Rearrange tabs, groups, and custom commands in the order you want. Create a new tab with your own commands. Add and remove groups on existing tabs. Export or import your personalized ribbon. What you cannot customise: You cannot change built-in commands, their names & icons. You cannot resize the Ribbon of the text size of commands. 2.2.8 CREATE A NEW TAB FOR THE RIBBON You can add and create your own tab to the Excel ribbon. In the Customize the Ribbon window, under the list of tabs, click the New Tab button. This will create a new tab named New Tab (Custom). Rename the Tab by clicking on Rename. ADVANCED INFORMATION TECHNOLOGY 59  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.2.13 Creating a new Tab 60 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2.2.9 ADD A CUSTOM GROUP TO A RIBBON TAB You can also add a new group to either a default or custom tab, as follows: In the right pane of the Customize the Ribbon window, select the tab to which you would like to add a new group. Click the New Group button. This adds a custom group, named New Group (Custom), at the bottom of the list of groups. To create a new group in a specific location, select the group after which the new group is to appear. Figure 2.2.14 Create Group You can rename this group through Rename option, also select an icon as shown below: ADVANCED INFORMATION TECHNOLOGY 61  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.2.15 Rename group ADD A COMMAND BUTTON TO EXCEL RIBBON Some Commands like Commands not in Ribbon can only be added to custom groups as follows: Create a custom group on an inbuilt or custom tab. In the list under Customize the Ribbon, select the target customer group. In the Choose commands from the drop-down list on the left, select the list from which you want to add commands, As an example Commands Not in the Ribbon. In the list of commands on the left, click the command you want to add. Click the Add button. Click OK to save the changes. As an example, we have added a Share Workbook (Legacy) command to Ribbon 62 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.2.16 Add Command Button 2.2.10 RENAME RIBBON TABS, GROUPS, AND COMMANDS You can give your own names to custom tabs and groups that you create, also you can rename the built-in tabs and groups. Spoiler: You cannot change names of the inbuilt commands, only commands added to custom groups can be renamed. On the right side of the Customize the Ribbon window, click on the item you want to rename. Click the Rename button below the list. In the Display name box, type the name you want, and click OK. Click OK to close the Excel Options window and view your changes. ADVANCED INFORMATION TECHNOLOGY 63  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.2.17 Rename Ribbon Tabs 2.2.11 MOVE RIBBON TABS, GROUPS, AND COMMANDS You can move tabs and groups on the Excel Ribbon in the most convenient places. However, the build-in commands cannot be moved, you can only change the order of commands in custom groups. In the list under Customize the Ribbon, click on the tab, group, or command in a custom group that you wish to move. Click the Up or Down arrow to move the selected item left or right on the ribbon, respectively. When the desired order is set, click OK to save the changes. Figure 2.2.18 Moving Ribbon Tabs 64 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2.2.12 HIDE AND SHOW TABS ON THE RIBBON You can hide from view any tabs that you never use. Similarly, you can show whenever you want. To hide a ribbon tab, simply uncheck its box in the list of tabs under Customize the Ribbon, Then click OK. To show a ribbon tab, select the box next to it, and click OK. You can show the Developer tab, which is not visible in Excel by default. Figure 2.2.19 Hide and show Tabs 2.2.13 EXPORT AND IMPORT A CUSTOM RIBBON You can export your Customised Ribbon settings to another PC or share your ribbon customizations with someone else. Export a custom ribbon: On the computer where you customized the ribbon, open the Customize the Ribbon window, click Import/Export, then click Export all customizations, and save the Excel Customizations.exportedUI file to some folder. ADVANCED INFORMATION TECHNOLOGY 65  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Import a custom ribbon: On another computer, open the Customize the Ribbon window, click Import/Export, select Import customization file, and browse for the customizations file that you saved. Figure 2.2.20 Import/Export Customisation Warning: When you import a customized ribbon to a specific PC, all prior ribbon customizations on that PC are lost. 2.3 QUICK ACCESS TOOLBAR The Quick Access Toolbar (QAT) is a small customizable toolbar at the top of the Excel window where you can keep the items that you not only need to access quickly but want to be immediately available independent of which of the Ribbon's tabs you are working on. Figure 2.3.1 Quick Access Toolbar 66 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS The QAT has beside it a drop-down menu containing a predefined set of the default commands, which can be displayed or hidden. Additionally, it consists of a choice to add your own commands. 2.3.1 CUSTOMIZING YOUR QUICK ACCESS TOOLBAR Apart from default options in the Quick Access Toolbar. You can customize your Quick Access toolbar. 2.3.2 MOVE QUICK ACCESS TOOLBAR BELOW OR ABOVE THE RIBBON Typically, the Quick Access toolbar appears on the left side of the title bar, above the Ribbon. Alternatively, you can display the Quick Access toolbar below the Ribbon; just right-click the Quick Access toolbar and choose Show Quick Access Toolbar below the Ribbon. Figure 2.3.2 Move QAT By default, the Quick Access Toolbar contains three tools: Save, Undo, and Redo. You can customize the Quick Access toolbar by adding other commands that you use often. What can be customised: Add your commands. Change the order of commands Move the QAT Add Macros to QAT Import Export your customisations What cannot be customised: Only command Icons can be displayed. QAT Buttons cannot be resized QAT is only in one line. 2.3.3 HOW TO CUSTOMISE QAT There are primarily 3 methods Method 1- File > Options > Quick Access Toolbar (QAT) ADVANCED INFORMATION TECHNOLOGY 67  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Method 2- Right Click on Quick Access Toolbar Right-click the Quick Access toolbar and choose to Customize the Quick Access Toolbar. Figure 2.3.3 Customise QAT Method 3- Click the down arrow beside QAT If you click the down arrow to the right of the Quick Access toolbar, you see a drop-down menu with some additional commands that you might want to place in your Quick Access toolbar. Apart from commands in Ribbon excel also has many commands which are not available in the ribbon which you can access by adding them to your Quick Access toolbar. Figure 2.3.4 Customise QAT 68 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS You see the Excel Options dialog box, shown in Figure 2.3.5 Figure 2.3.5 Customise QAT 2.3.4 ADD A COMMAND TO QUICK ACCESS TOOLBAR On the left pane in the above Figure, you can see Choose commands from when you click the dropdown, you can see many options, you also have options like “Commands Not in Ribbon” Once you select an option you can click on add to include in Quick Access Toolbar (QAT) Figure 2.3.6 Adding a Command to QAT ADVANCED INFORMATION TECHNOLOGY 69  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Another method to add a command from the Ribbon to your Quick Access toolbar, right-click the command and choose to Add to Quick Access Toolbar. Figure 2.3.7 Adding a Command to QAT You can also enable command from the predefined list. To enable a currently hidden command from the predefined list, you can click the Customize Quick Access Toolbar button (the down arrow). In the list of the displayed commands, click the one you wish to enable. Figure 2.3.8 Enable Command from list 2.3.5 ADD MACROS TO QUICK ACCESS TOOLBAR You can add Macros to the Quick Access Toolbar as follows Open the Customize the Quick Access Toolbar window. In the Choose commands from the drop-down list on the left, select Macros. In the list of macros, select the one you wish to add to the Quick Access Toolbar. Click the Add button. 70 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Click OK to save the changes and close the dialog box. Figure 2.3.9 Adding a Macro to QAT 2.3.6 HOW TO REMOVE A COMMAND FROM QUICK ACCESS TOOLBAR You can also remove either a default or custom command from the Quick Access Toolbar, just right-click QAT and pick “Remove from Quick Access Toolbar from the menu Figure 2.3.10 Remove from QAT Another method to remove it by selecting the command in the Customise Quick Access Toolbar window, and then click the Remove button. 2.3.6 REARRANGE COMMANDS ON QUICK ACCESS TOOLBAR You can change the order of the QAT commands: Open the Customize the Quick Access Toolbar window. ADVANCED INFORMATION TECHNOLOGY 71  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Under Customize Quick Access Toolbar on the right, select the command that you want to move, and click the Move Up or Move Down arrow. Figure 2.3.11 Moving commands in QAT 2.3.7 GROUP COMMANDS ON QUICK ACCESS TOOLBAR There could be many commands on the Quick Access toolbar. You can sub-divide them into logical groups, for example, Home and Data Commands, you can group commands by adding a separator. Open the Customize the Quick Access Toolbar dialog window. In the Choose commands from the drop-down list on the left, pick Popular Commands. In the list of commands on the left, select and click Add. Click the Move Up or Move Down arrow to position the separator where needed. Click OK to save the changes. 72 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.3.12 Group Commands in QAT 2.3.8 RESET QUICK ACCESS TOOLBAR TO THE DEFAULT SETTINGS You can discard all your customizations and revert the QAT back to its original setup as follows: Open the Customize the Quick Access Toolbar window. Click the Reset button, and then click Reset only Quick Access Toolbar. Figure 2.3.13 Reset QAT 2.3.9 EXPORT AND IMPORT A CUSTOM QUICK ACCESS TOOLBAR You can save your Quick Access Toolbar and ribbon customizations into a file that can be imported later. ADVANCED INFORMATION TECHNOLOGY 73  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Through this, you can keep your Excel interface looking the same on all the computers that you and share it with others. Export a customized QAT: In the Customize the Quick Access Toolbar window, click Import/Export, then click Export all customisations, and save the customisations file to some folder. Import a customized QAT: In the Customize the Quick Access Toolbar window, click Import/Export, select Import customisation file, and browse for the customisations file that you saved earlier. Figure 2.3.14 Import/ export Customisation Warning: When you import a customized QAT to a specific PC, all prior ribbon customizations on that PC are lost. 2.4 DATA TYPES IN EXCEL Excel workbook can contain may worksheets and each worksheet can contain Billions of cells, each of these cells can contain primarily three types of data in cells: labels (Text), values (Numbers), and formulas. A worksheet can also contain charts images etc which are not in cells, but on a Virtual draw layer on top of every sheet. Values (Numbers) Numeric values speak of an amount of some sort: Net Profit, Turnover, Dates, even time are all numbers in excel. Excel aligns Numbers to the right side of the cell. 74 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Excel’s numbers are precise up to 15 digits it cannot handle numbers larger than that. Numbers that are entered that contain more than 15 digits will be truncated. That is, digits will be truncated off the right-hand side and replaced with zeros. These 15 Digits are pretty enough in most circumstances, but issues do crop up in Bank account Numbers which are often larger than 15 digits or credit card numbers which are often 16 digits, in these cases, it should be entered as text. Labels (Text) Text are descriptive pieces of information, such as names, years, or other identifying statistics, and they usually include letters of the alphabet, numerical characters, symbols such as % and $, a s well as spaces and tabs are all valid text. Excel aligns text to the left side of the cell. Formulas Formulas are instructions for Excel to perform calculations. Excel aligns formulas to the left side of the cell and starts with an “=” sign. Cells could have logical values (TRUE or FALSE, also called Boolean values) 2.4.1 DATE AND TIME Dates and times are also stored as number types by Excel. Dates are stored as the number of days since the date 1/1/1900. In other words, January 1, 1900, is considered by Excel to be 1. Therefore, 1/2/1900 would be stored as 2, 1/3/1900 as 3, and so on. Remember that Excel does not recognize dates BEFORE 1/1/1900. Excel treats Times as fractions of days. Since a day is 24 hours in length, then 1/4th of a day (0.25) would be 6 hours. Since each day begins at 0 hours and ends 24 hours later, Excel would store the date and time for 6:00 AM on 1/1/1900 as 1.25. As an example, if it is 09:00 AM on July 1st, 2020. Excel would store the current date and time as 44013.375. That is, forty thousand Four-hundred Thirteen and (roughly) 38/100ths days since 1/1/1900. 2.4.2 VALUES VS FORMATS It is important to differentiate the concept of value and format in excel. Since the excel window dresses a number to be displayed differently. The way Excel displays a given numerical value within a cell is known as formatting. As an example, let us consider the following: Displayed in Value in Cell Format Cell 1.00 General 1.00 1.00 Number (4 digit decimal precision) 1.0000 1.00 Currency ₹ 1.00 1.00 Percent 100% 1.00 Date 01-01-1900 0.75 Fraction 3/4 0.75 Time 18:00:00 ADVANCED INFORMATION TECHNOLOGY 75  DATA ANALYSIS USING EXCEL AND CAAT TOOLS The value of a number is stored in the cell. Formatting determines how the number is displayed, and what level of precision is displayed. When using the value of the cell in calculations, the true value is used, not the displayed value 2.4.3 NEW DATA TYPES IN EXCEL Traditionally in Excel, one cell could only contain one piece of data. You can now have multiple data fields inside one cell. The new data types which Excel has introduced are really connections to an online data source that provides more information about the data. At present, there are two data types available in Excel: Stocks and Geography. Many more are being released. Figure 2.4.1 New Data types 2.4.4 STOCKS The new stock data type of our cell might display the name of a company but will also contain information like the current share price, trading volume, market capitalization, employee headcount, and year of incorpor ation for that company. You will need to select the range of cells with your text data. Go to the Data tab. Click on the Stock data type. This will convert the plain text into a rich data type. 76 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.4.2 Stock Data Type Figure 2.4.3 Extracts the relevant data ADVANCED INFORMATION TECHNOLOGY 77  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Currency & Cryptocurrency Data With the stock data type, you can not only obtain stocks from a company name but can also be used for currency and cryptocurrency conversions. Figure 2.4.4 Currency data through Stock 2.4.5 GEOGRAPHY The geography data type supports most types of geographies. Countries, states, provinces, counties, and cities. display the name of a company but will also contain information like the Area, Population, Latitude, Longitude, and various other parameters for that Geography. You will need to select the range of cells with your text data. Go to the Data tab. Click on the Geography data type. This will convert the plain text into a rich data type. Click on insert and you can insert any desired parameter. 78 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.4.5 Geography data type can extract any geographical data 2.5 EXCEL OPTIONS AND CUSTOMISATIONS Excel is an out of the box global product. When it comes to Excel, the default settings work fine in most of cases. But if need be, you can easily customize Excel through Excel Options. File > Options Open the File menu and select Options from the left navigation pane to open the Excel Options dialog box. ADVANCED INFORMATION TECHNOLOGY 79  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.5.1 Excel options The dialog box has categories for General, Formulas, Data, Proofing, Save, Language, Ease of Access, Advanced, Customize Ribbon, Quick Access Toolbar, Add-Ins, and Trust Center. General The most used settings, such as user interface settings, the default font for new workbooks, number of sheets in a new workbook, customer name, and Start screen. Formulas All options for controlling calculation, error checking rules, and formula settings Data The data category offers the new Edit Default Layout for pivot tables, several other pivot table options, and then a series of checkboxes to bring back the legacy Get Data categories. Proofing Spell-check options and a link to the AutoCorrect dialog box. Save The default method for saving, Auto Recovery settings, legacy colours, and web server options. Language Choose the editing language, ToolTip language, and Help language. Ease of Access Options available are Provide Feedback with Sound, Provide Feedback with Animation, Screen Tip Style, and the default document font size. Advanced All options that Microsoft considers advanced, spread among 14 headings. Customize Ribbon Icons to customize the ribbon. Quick Access Toolbar Icons to customize the Quick Access Toolbar (QAT). Add-Ins A list of available and installed add-ins. New add-ins can be installed from the button at the bottom of this category. 80 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS HELP WITH SETTINGS Many settings appear with a small icon. If you hover the mouse near this icon, Excel displays a super ToolTip for the setting. The ToolTip explains what happens when you choose the setting. It also provides some tips about what you need to be aware of when you turn on the setting. As an example, In figure 2.5.2, The Tooltip tells about Mini Toolbar Figure 2.5.2 Help with settings Some of the useful Excel options are as follows: 2.5.1 CHANGE THE DEFAULT FONT TYPE AND SIZE AND NUMBER OF WORKSHEETS By default, Excel uses Calibri (body font) font size 11. You can, however, select a different Default font type and size as follows: File> Excel Options> General. In General options, within ‘When creating new workbooks’ section, change the default font type and font size. You can also change the default number of sheets in workbook ‘Include this many sheets’ to 1 Figure 2.5.3 Change the number of sheets ADVANCED INFORMATION TECHNOLOGY 81  DATA ANALYSIS USING EXCEL AND CAAT TOOLS 2.5.2 AUTOMATICALLY INSERT DECIMAL POINTS DURING DATA ENTRY AND ENABLE FILL HANDLE It can be a very handy option for you if you do a lot of data entry with decimals. When you enter numeric values with 2 decimal points, instead of typing 123.45, you can simply type 12345, and Excel would automatically insert 2 decimal places. File> Excel Options> Advanced. In Editing Options, select ‘Automatically insert a decimal point’ and specify the places. Select ‘Enable fill handle and cell drag-and-drop’ Figure 2.5.4 Enable fill handle 2.5.3 AUTOMATICALLY COMPLETE ABBREVIATIONS There is a feature in Excel where you can specify shorthand text and Excel will automatically complete it for you. For example, whenever you type ICAI, Excel would automatically replace it with The Institute of Chartered Accountants of India. File> Excel Options> Proofing Click on AutoCorrect Options button In the AutoCorrect dialog box, type what you want to Replace (in Replace field) with what you want to replace it with (in With field) Click on Add then OK 82 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.5.5 Auto complete abbreviations 2.5.4 USING AUTORECOVER OPTIONS AND DEFAULT FILE LOCATION Excel periodically saves a copy of your work every 10 minutes, you change that time If your computer crashes, the recovery pane offers to let you open the last AutoRecovered version of the file. This feature is sure to save you from retyping data that might have otherwise been lost. You can also specify the default location where your file is to be saved. File> Excel Options> Save Select Save Autorecover and specify the value 5 Select Save to Computer by default and specify the location. ADVANCED INFORMATION TECHNOLOGY 83  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.5.6 AutoRecover Options 2.5.5 CREATE CUSTOM LIST You can create your own custom lists, Details have been explained in Topic Sort TRUST CENTER 2.5.6 PROTECTED VIEW FOR FILES ORIGINATING FROM THE INTERNET The files from the Internet or Outlook initially open in protected mode. This mode gives yo u a chance to look at the workbook and formulas without having anything malicious happen. If you only want to view or print the workbook, the protected mode works great. After you click Enable Editing, Excel will skip protected mode the next time you open the file. This protected mode can be enabled or disabled as follows File> Excel Options>Trust center>Protected View Figure 2.5.7 Protected view 84 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2.5.7 TRUSTED DOCUMENT SETTINGS By default, Excel warns you about all sorts of things. If you open a workbook with macros, links, external data connections, a message bar appears above the worksheet to let you know that Excel disabled those “threats.” If you declare a folder on your hard drive to be a trusted folder, you can open those documents without E xcel warning you about the items. File> Excel Options>Trust center>Trusted Locations Click on ‘Add a new location’ specify the path. You can also make the subfolders trusted. Figure 2.5.8 Trusted Location 2.6 FIND AND SELECT BLANKS, VISIBLE CELLS, NUMBERS, ERRORS You often receive an Excel file from a client or import it from an external database. You are not sure about the integrity of data thus it is important to check the data to make sure there are no Blanks or Formula errors, or Numbers stored as text and so forth. In a small dataset It could be still easy, but if you have a huge file containing hundreds or even thousands of rows, pinpointing them manually is next to impossible. All this is possible since Excel provides a dedicated dialog box to access special groups of cells, called "Go To Special". Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. There are various methods to access it Method 1 1. On the Home tab, in the Editing group, click Find & Select. ADVANCED INFORMATION TECHNOLOGY 85  DATA ANALYSIS USING EXCEL AND CAAT TOOLS 2. Click Go To Special. Figure 2.6.1 Go to Special on Home Method 2 1. To access this dialog with the keyboard, type Control + G, 2. Click the Special button (or use Alt + S) on Windows. Method 3 1. Press Function key F5 2. Click on Special button Go To Special has various selection features 86 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.6.2 Go to Special Dialog Box 2.6.1 NOTES Action: Selects all cells with Notes Audit Tip: A quick way of finding all cells with Notes, particularly useful if you want to clear all Notes from your worksheet 2.6.2 CONSTANTS Action: Selects all cells containing constants Options: Numbers: Selects all cells with constants that are numbers Text: Selects all cells with constants that are text Logical: Selects all cells with constants that are logical (TRUE or FALSE) Audit Tip: The number constants in your spreadsheet should all be inputs. Highlighting all constants is a great way of checking the structure of your spreadsheet and you can keep it in a different colour to highlight cells where inputs need to be made. Selecting text could also indicate Numbers stored as Text. When auditing – select all constants and change the fill colour. This instantly gives you visibility of your model inputs and flags any inconsistencies. ADVANCED INFORMATION TECHNOLOGY 87  DATA ANALYSIS USING EXCEL AND CAAT TOOLS 2.6.3 FORMULAS Action: Selects all cells containing formulas Options: Numbers: Selects all cells with formulas that return numbers Text: Selects all cells with formulas that return text Logicals: Selects all cells with formulas that return logicals (TRUE or FALSE) Errors: Select cells which contain formula errors Audit Tip: Highlighting all the formulas within your spreadsheet can help in protecting or hiding the formulas. It can also be used to highlight formula errors in the sheet. 2.6.4 BLANKS Action: Selects all blank cells Audit Tip: A quick way to select all blank cells. This is useful if you want to quickly format all blank cells or as a way of identifying cells that look blank but contain a constant or formula (i.e. with white on white formatting). 2.6.5 VISIBLE CELLS ONLY Action: Selects cells that are not hidden (& therefore are visible) Audit Tip: Useful if you only want to select the non-hidden cells and Copy them somewhere else for example in the filter. CASE STUDY: In an audit, your principal CA Chandiwala gives you a division wise quarterly sales report as shown in Fig received from a client. You are to find inconsistencies in terms of: are blank cells really blank, any numbers stored as text, any errors in formulas? Figure 2.6.3 Quarterly sales report 88 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Strategy: We can analyse the file using ‘Go To Special’ as follows: 1. Select the range where you want to highlight. To select all cells with data, click the upper -left cell and press Ctrl + Shift + End to extend the selection to the last used cell. 2. Press F5 3. Click Special Figure 2.6.4 Press F5 Go To Special Finding Blanks 4. In the Go To Special dialog box, select Blanks and click OK. 5. This will select all empty cells in the range. Figure 2.6.5 Select Blanks ADVANCED INFORMATION TECHNOLOGY 89  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.6.6 Blanks Highlighted 6. If you observe cell C4 seems to be blank but it is not showing as blank. 7. If we check then we find someone has formatted the number in white so will not appear in printing but will affect the result of any operation like sum Figure 2.6.7 Cell has a value Numbers stored as text 1. Again, highlight the range > Press F5 or CTRL +G >Special 2. In the Go To Special dialog box, select Constants and under options select Text 3. Click OK. 4. This will select all Text cells in the range which in this case are Numbers stored as Text 5. If you observe in the formula bar, you can see a quote before number indicating that the number has been stored as text 90 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.6.8 Go to Special Text Figure 2.6.9 Text Highlighted Errors in Formula 1. Again highlight the range > Press F5 or CTRL +G >Special 2. In the Go To Special dialog box, select Formulas and under options select Errors 3. Click OK. 4. This will select all Formula Error cells in the range. ADVANCED INFORMATION TECHNOLOGY 91  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.6.10 Go To Special Errors Figure 2.6.11 Errors Highlighted Gist: We have analysed the Excel data to find inconsistencies using the feature Go To Special. Commands learnt: Find and select > Go to Special Food for thought: Go To Special Go To Special only selects cells in the current worksheet rather than the whole workbook. Go To Special searches within the selected range, if you want to select the entire worksheet to ensure that only one cell is selected. 92 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2.7 SMART COPY PASTE IN EXCEL Often while working in Excel, you would need to copy and paste information from one cell to another , or one range to another. You would all be familiar with Copy using a shortcut CTRL + C and pasting at another location using CTRL + V. In Excel you can Copy a cell to another location, Copy a cell to a range ie the same cell is copied to all cells in the range, also copy a range to another range if they are the same size. The normal Copy operation can be done by using Ribbon command Home >Clipboard >Copy transfers a range of cells to the clipboard for pasting to a different location Home >Clipboard >Paste Figure 2.7.1 Copy Paste When you copy a cell or range, Excel surrounds the copied area with a moving border. If that border remains moving, the copied information is available for pasting. If you press Esc to cancel the animated border, Excel removes the information from the Clipboard. Also, once the information is pasted the information is removed from Clipboard. In case you want to Smart Tip: Smart Copy Paste However, if you click the Copy button more than once before you click the Paste button, Excel may automatically display the Office Clipboard taskbar. Or You can click on the dialog launcher on the bottom right of the Home >Clipboard group to toggle the Clipboard task pane on and off. ADVANCED INFORMATION TECHNOLOGY 93  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.7.2 Activate Clipboard When you copy information to the Office Clipboard, it gets appended to the information to the Office Clipboard instead of replacing what is already there. With multiple items stored on the Office Clipboard, you can then paste the items either individually or as a group. Case Study: In an audit, your principal CA Chandiwala gives you a workbook containing four sheets for different quarters which he wants you to append into a single sheet so that that annual data can be analysed. Figure 2.7.3 Sheet with 4 Quarters Strategy: We can Append the sheets into one annual data as follows: 1. Click on the dialog launcher on the bottom right of the Home >Clipboard to activate Clipboard 94 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 2. Copy the data from Sheet Q1, you will see it shows in the clipboard. 3. Similarly, we will keep on copying the data from Q2, Q3, and Q4 and the data appears on Clipboard. 4. Now if you want to copy all of them in one go. You have an option of Paste all. Figure 2.7.4 Copied to Clipboard 5. The whole years' data is pasted at once ADVANCED INFORMATION TECHNOLOGY 95  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.7.5 Data of 4 Quarters pasted in one go Gist: We have appended the data from various ranges into one sheet using Paste all feature in Clipboard. Commands learnt: Home > Clipboard 2.7.1 PASTE SPECIAL In Excel Paste Special is a feature through which apart from pasting a whole cell, you can paste only a certain attribute such as value, formula, formatting, column width, or note. Excel's Paste Special offers a wide range of options to paste only specific elements of the copied cells or perform a mathematical operation with the copied data. 96 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.7.6 Paste Special You can use the Paste Special through following steps Copy the source cell or a range of cells CTRL +C Select the destination cell(s). Open the Paste Special dialog. Select the desired paste option, Click OK or press the Enter key. There are various methods to open Paste Special Dialog Method 1 Home> ClipBoard> Paste Click on Dropdown Click Paste special ADVANCED INFORMATION TECHNOLOGY 97  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.7.7 Paste Special on Home Tab Method 2 Right-click a cell where you want to paste the copied data, Click Paste Special in the context menu. Figure 2.7.8 Paste Special through right-click Hover over the Paste Special item in the context menu, A fly-out menu will show up offering 14 more paste options 98 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.7.9 Paste Special Method 3 The fastest way is to use the shortcut ALT+E+S 2.7.2 HOW TO USE PASTE SPECIAL There are various ways in which Paste special can be used Values You can Paste Values rather than underlying formula Values Figure 2.7.10 Paste special values Comments & Notes You can copy only Comments or Notes ADVANCED INFORMATION TECHNOLOGY 99  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.7.11 paste Special Comments Transpose You can change columns to rows in Excel using Paste Transpose Option Figure 2.7.12 Paste Special Transpose Column Width You can Paste only column width; it is a quick way to have the destination cell with the same column width. Figure 2.7.13 Paste Special Column Width Paste and add/subtract/multiply/divide at a time You can paste as well as perform operations like add, subtract, multiply or Divide on all destination cells at the same time. 100 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.7.14 Paste Special Multiply Case Study: In an investigation, your principal CA Chandiwala gives you a workbook containing Data consisting of Name, emailed, Sales. You have to calculate GST and Total sales which you have to compare with total GST deposited. Since the data has been extracted in ASCII format and imported to Excel, the Numbers are stored as text, performing any calculations is a challenge. You are to perform the following: (i) Remove the hyperlinks in the email id of the customer. (ii) Convert Numbers stored as text to Numbers. (iii) Calculate GST and Total sales Strategy: We can use the power of Paste Special for all the above tasks: Paste + Multiply(*)can be used to remove all hyperlinks in your worksheet in one go 1. Select cells F1 which is a Blank 2. Press Ctrl + C to copy them. 3. Select the Email, cells B2:B8 4. Press the paste special shortcut (ALT + ES), 5. Select Multiply under Operations, or press M. 6. This will multiply each of the amounts in column B by value in F1. 7. Click Enter resulting in removing all Hyperlinks. ADVANCED INFORMATION TECHNOLOGY 101  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.7.15 Paste Special Multiply Paste + Multiply can be used to convert Numbers stored as text in a range in one go 1. Select cells F1 which has a value 1 2. Press Ctrl + C to copy them. 3. Select the Email, cellsC2:C8 4. Press the paste special shortcut (ALT + ES), 5. Select Multiply under Operations, or press M. 6. This will multiply each of the amounts in column B by a value 1. 7. Click Enter resulting in converting all Numbers stored as text to Numbers. Figure 2.7.16 Paste Special Multiply Paste + Multiply can be used to Multiply all the cells in the destination with the copied value. 1. Select cells F1 which has a value.12 ie GST Rate 12% 2. Press Ctrl + C to copy them. 3. Select the Email, cells D2:D8 4. Press the paste special shortcut (ALT + ES), 102 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 5. Select Multiply under Operations, or press M. 6. This will multiply each of the amounts in column B by a value of 0.12. 7. Click Enter resulting in converting all Values to GST. Figure 2.7.17 Paste Special multiply 1. Similarly Multiplying with 1.12 can give you total Sales Figure 2.7.18 Paste Special Gist: We have removed Hyperlinks, Converted Numbers stored as Text to Numbers and Calculated Percentage Values using Paste Special feature in Clipboard. Commands learnt: Home > Clipboard> Paste Special 2.8 MANAGING WORKSHEETS Excel offers a convenient way to manage data. As an example, as an Accountant, you might have on the table with your firm’s yearly sales, another with expenses, and third analyzing profitability and another one with Balance sheet figures and probably one with Forecasts. If you create these tables in different spreadsheets, you must copy the information you want the sheets to share from one location to a nother, all without misplacing a number or making a mistake. Excel offers a neat way to do it through Workbooks. In Excel, each file is called a workbook, and each workbook can contain one or more worksheets. ADVANCED INFORMATION TECHNOLOGY 103  DATA ANALYSIS USING EXCEL AND CAAT TOOLS A workbook can be thought of a book containing many sheets of paper and worksheet is akin to a single sheet of paper in that book. 2.8.1 2.8.1 ADDING AND REMOVING WORKSHEETS When you start a fresh workbook in Excel, you get a single blank worksheet. To add more sheets, you need to click the “New sheet” button, which is a small plus-in-a-circle icon that appears immediately to the right of your last worksheet tab. Figure 2.8.1 New Sheet If you continue adding worksheets, you will eventually find that all the worksheet tabs will not fit at the bottom of your workbook window. If you have more worksheets than fit into the tab list, you will need to use the scroll buttons, which are immediate to the left of the worksheet tabs) to review the list of worksheets. The figure shows the scroll buttons. You can scroll across the sheet through these buttons. Tip: If you want to see all sheets in one go, just Right-click on the right scroll Button Figure 2.8.2 Right Click to see sheet You want to Remove a worksheet, simply move to the sheet you want to remove, and then choose Home>Cells>Delete>Delete Sheet (you can also right-click a tab, and then choose Delete). 2.8.2 MOVING BETWEEN SHEETS To move from one worksheet to another, you have different Methods: 104 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Click the worksheet tabs at the bottom of Excel’s grid window (just above the status bar). Use Shortcut Ctrl+Page Down to move to the next worksheet. Use Shortcut Ctrl+Page Up to move to the previous worksheet. 2.8.3 HIDING WORKSHEETS You can also hide a worksheet temporarily. When you hide a worksheet, its tab disappears, but the worksheet itself remains part of your workbook file, available whenever you choose to unhide it. You cannot print a hidden worksheet, either. To hide a worksheet, right-click the worksheet tab, and then choose Hide. To redisplay a hidden worksheet, right-click any worksheet tab, and then choose Unhide. The Unhide window appears along with a list of all hidden sheets, as shown in Figure. Select a sheet from the list, click OK to unhide it. Figure 2.8.3 Hidden Sheets Case Study: In an ongoing Forensic audit your principal CA Chandiwala gives you a workbook containing a Bank Reconciliation which is reconciled but when he crosschecks Cheques issued not presented at” 'ITGI Ann1'!D14” and goes to the sheet where the details of pending cheques is there, he finds totals are not matching but the reference is correct, as per attached Figure He is perplexed and gives you the workbook to find if there is any fraud involved. How will you check the authenticity of the sheets and integrity of the workbook? ADVANCED INFORMATION TECHNOLOGY 105  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.8.4 Excel Reconciliation Sheet Strategy: This is a case where everything seems to be correct, Cell reference is correct, but still, figures are not matching. This is how fraudster creates an illusion. If you are not cautious, you are bound to be misled. As a part of checking the integrity of workbooks, it is always a good control to check for hidden sheets. In this case when you check for Hidden sheets 1. Right-click on any tab. 2. Select Unhide 3. You find there is another hidden sheet with a matching name Figure 2.8.5 Unhide sheets 106 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS 4. Click Unhide 5. The sheet is unhidden Figure 2.8.6 Similar Names 6. Now you have discovered the sheet from where the value is being referenced. 7. But there is another challenge you now have two sheets with the same name ITGI Ann1 8. In Excel, two sheets cannot have the same name. 9. If you observe closely, in one it is “L” in lower case and the other one consists of “I” in upper case visually they look similar. 10. You discover how the fraudster has created the illusion. Gist: You have understood how to hide and unhide sheets, and as a control to check the integrity of the workbook. Commands learnt: Home > Cells>Format>Hide/Unhide 2.8.4 REARRANGING WORKSHEETS You can easily rearrange any of your sheets just by dragging their tabs from one place to another, as shown in Figure 2.8.7 ADVANCED INFORMATION TECHNOLOGY 107  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Figure 2.8.7 Rearrange Sheets Tip: You can use a similar technique to create copies of a worksheet. Click the worksheet tab and begin dragging, just as you would to move the worksheet. Before you release the mouse button, press the Ctrl key (you will see a plus sign [+] appear). Keep holding the Ctrl key until you release the mouse button, at which point Excel creates a copy of the worksheet in the new location. 2.8.5 MOVING AND COPYING WORKSHEETS FROM ONE WORKBOOK TO ANOTHER You may want to move (or copy) a worksheet from one Excel file to another, as follows: Open both spreadsheet files. The file that contains the worksheet you want to move or copy is called the source file; The other file where you want to place the worksheet copy is the destination file. Go to the source workbook. Right-click the worksheet you want to transfer, From the shortcut menu that appears, choose Move or Copy. o To transfer multiple worksheets at once, hold down the Ctrl key, > select all the worksheets you want to move or copy. o Excel highlights all the worksheets you select (and groups them together). o Right-click the selection, and then choose Move or Copy. Choose the destination file from the “To book” drop-down list. The “To book” menu shows all the currently open workbooks (including the source workbook). Specify where you want to insert the worksheet. If you want to copy the worksheet, turn on the “Create a copy” checkbox at the bottom of the window. Click OK 108 ADVANCED INFORMATION TECHNOLOGY  EXCEL BASICS AND CUSTOMISATIONS Figure 2.8.9 Moving/ Copying Sheets Tip: You can also move worksheets to a new workbook, which it automatically creates for you. To do so, choose “(new book)” in the “To book” list. The resulting workbook has only the worksheets you transferred to it. 2.9 MANAGING ROWS AND COLUMNS Every worksheet has exactly 1,048,576 rows and 16,384 columns. 2.9.1 INSERTING ROWS AND COLUMNS The number of rows and columns in a worksheet is fixed, you can still insert and delete rows and co lumns if you need to make room for additional information. These operations do not change the number of rows or columns. Instead, inserting a new row moves down the other rows to accommodate the new row. The last row is simply removed from the worksheet if it is empty. Inserting new column shifts the columns to the right, and the last column is removed if it is empty. There are few methods to insert rows or columns Method 1 Select an entire row or multiple rows by clicking the row numbers in the worksheet b order. Right-click and choose Insert from the shortcut menu. Method 2 Move the cell pointer to the row that you want to insert, and then choose Home > Cells > Insert > Insert Sheet Rows. If you select multiple cells in the column, additional rows that correspond to the number of cells selected in the column are inserted and moves the rows below the insertion down. Columns can be inserted similarly by clicking on Column where you want to insert. ADVANCED INFORMATION TECHNOLOGY 109  DATA ANALYSIS USING EXCEL AND CAAT TOOLS 2.9.2 DELETING ROWS AND COLUMNS You can also delete rows or columns. To delete a row or multiple rows Select the rows, Right-click, and choose Delete from the shortcut menu. Another method is through ribbon option Home >Cells > Delete Sheet Rows. The same process can be used for deleting columns, Select the column(s) and choose Delete from the shortcut menu. 2.9.3 HIDING ROWS AND COLUMNS You can hide rows or columns. Hiding rows and columns may be useful if you do not want users to see some particular information, or you do not want to print it. To hide rows in your worksheet Select the row or rows that you want to hide by clicking in the row header on the left. Then right-click and choose Hide from the shortcut menu. Another method using Ribbon commands on the Home > Cells >Format > Hide & Unhide drop-down list. 2.10 SUMMARY Excel is a versatile tool for data manipulation, analysis, report building, visualisation. In this chapter, you learned the basics of Excel and a basic overview of the screen including an understanding of Ribbons. You have learned that you can increase efficiency by using Quick Access Toolbar. You have also learnt about various Data types and the new rich data types like Stocks and Geography. Excel offers a host of features for customisation options. You learned how Go to Select can assist you in Looking for consistencies, finding irregularities, and clean data. Finally, you learnt how to smart copy and paste including various options in paste special In addition, we learned to manage worksheets, rows, and columns. References Michael Alexander, Richard Kusleika, John Walkenbach, ‘Excel 2019 Bible’, Wiley,2018 Greg Harvey, ‘Excel 2019 All-in-One for Dummies, John Wiley & Sons,2018 www.chandoo.org 110 ADVANCED INFORMATION TECHNOLOGY  CHAPTER 3 EXCEL – MAKING DATA CONSISTENT LEARNING OBJECTIVES ◼ Understanding Naming of cells, ranges ◼ Understanding insertion of Tables in Excel ◼ Understanding Data Validation features of Excel ◼ Understanding creation of Dependent Drop-Down list ◼ Understanding INDIRECT Function 3.1 INTRODUCTION Consistency is an important feature in Excel and makes the Excel sheets more meaningful and understandable. In this chapter you will learn how Naming a Cell or a range makes the formulas more understandable for any user of the sheet. Tables feature in Excel brings consistency in format, formulas, and makes the tabular data more manageable. Data Validation is an outstanding feature in Excel which aids in defining restrictions on what data can or should be entered in a cell. 3.2 NAMING IN EXCEL In real world you are more used to names whether for people, objects or location. You understand Delhi more easily rather than some Latitude & Longitude reference, same way dealing with cryptic cell and range addresses can sometimes be confusing. Excel allows you to assign human understandable descriptive names to cells and ranges. Rather than saying A2:A50 it would be more meaningful to call it Sales and while referring in formulas, you can have a formula =SUM(Sales) rather than =SUM(A2:A50). Advantages Navigation becomes easy with names to areas of your worksheet either by using the Name box, or pressing F5 Key and specifying the range name. Figure 3.2.1 Name Box  DATA ANALYSIS USING EXCEL AND CAAT TOOLS Chances of error reduce when writing a name rather than cell addresses. Writing formulas become much co