Scribd
Upload
3 views36 pages

Chapter - 2

The document provides a comprehensive guide on data analytics using spreadsheets, focusing on Excel's features, functions, and shortcuts for effective data manipulation and visualization. It covers the structure of spreadsheets, performing computations, statistical analysis, and various Excel functions for data aggregation and reporting. Additionally, it includes practical tips for enhancing productivity through keyboard shortcuts and mouse techniques.

Uploaded by

aurobindos009
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
3 views36 pages

Chapter - 2

The document provides a comprehensive guide on data analytics using spreadsheets, focusing on Excel's features, functions, and shortcuts for effective data manipulation and visualization. It covers the structure of spreadsheets, performing computations, statistical analysis, and various Excel functions for data aggregation and reporting. Additionally, it includes practical tips for enhancing productivity through keyboard shortcuts and mouse techniques.

Uploaded by

aurobindos009
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
UNIT-IL DATA PREPARATION, SUMMARIZATION AND VISUALIZATIONS. USING SPREADSHEET Data Analytics Using Spreadsheet 2.1 Basics of Spreadsheet 2.1.1 Structure of Spreadsheet CHAPTER 2.1.2. Using Excel for Spreadsheet 2.2, Working through Shorteuts in Spreadsheet 2.1 Keyboard Short Cuts 2.2.2 Mouse Tips and Tricks 2.3. Working with Workbook and Worksheet 2.3.1 Performing simple computations 2.3.2. Funetions and Formulae 2.3.3 Aggregation using spreadsheet 23.4 Summing functions and reporting in spreadsheet 24 Statistical Analysis using Spreadsheet 24.1 Descriptive Statistics 242. Testing the Normality of Distribution 243 Covariance and Correlation matrix 24.4 Moving Averages 24.5 Estimates of Linear Regression Model 24.6 Interpretation of Results 24.7 Tests for Heteroscedasticity and Multicollinearity + Exercises * Answers A spreadsheet is a computer program that has been designed to organize, analyze, and manipulate data in a matrix (or grid-like) format. Therefore, it is an electronic upgrade to the classic paper accounting sheets. The following basic features explain all about spreadsheets: + Data Organization: Spreadsheets are built on a matrix (or grid) of rows and columns, such that the intersection of a row and column is called a eel. The user can impute text, numbers, dates, formulas or even functions (the ready-to-use programs) into these cells. BUSINESS ANALYTicg, ce to ack nome ENPEMSS, ang inventory, and make data-drive, Project Managemen, nimi Leaeare pe ack Te sh re owe oo Wed By People ATOSS Various Sera uated with poplar spreadsheet applications likg le aca eyOrRenET a 22.1 Structure of Spreadsheet isa document, which is formed as amatrx (or grid) of Rows and Columns to record and ‘a incomes and expenses, debits and credits, inventory and so on, Spreadsheet, the management of numbers and their calculations. Electronic spreadsheets ‘calculate totals, averages, percentages, budgets, and complex financial, scientific formulas and alot DATA ANALYTICS USING SPREADSHEET 23 space that is going up and down the window. ‘The highlighted part of the Excel spreadsheet space that is going across the window. Numbers location. The highlighted part ofthe spreadsheet in green colour the space where a specified row and column intersect. Tis cell wv» H20) contain a function because they add together the ‘So the function in H112 looks like this ~SUM(B12:G12), ll, program to add together the HAZ. Although this funct calculations by using mathems BUSINESS ANALY Tig | DATA ANALYTICS USING SPREADSHEET 28 user to create charts and graphs charts render complex data ‘of information, rsatile because of being which is the ‘Worksheets are great for ‘worksheets have emerged as Tw 1 Scrat tng norton crn cre Te nes Oa lc DOM) vee it atbooks na Werth: er an sve bene ee ma ret me ns rac saonie ren PwIOG.| es ewe magento MI 88 re (e) © and Sharing: When several persons 4 ing together on aparticular project, 4g manage related data and information oF ‘separate datasets, in an organized manner. ‘The user can aaa mechanism is the nood of the hour. Spreadsheets are easy to share with other navigate across multiple workbooks aso. ‘cao emis or clients. Many spreadshet programs alow multiple participants fo work o% Ty speedup working thovgh Excel worksheets, several Keyboard shortcuts and Mouse Tips ar the same sends iol ty, which Fe them use for temmwork. available that need to be ‘understood by users of Excel worksheets, a5 explained below: apr fom er cst progam * oe tandsvong 224 Keybourd Shorts in MS Exe functions (SUM, VLOOKUP Excel shortcuts are keyboard combinations that al tn fe milan efit Thy ean save we ES (@) Flexibility and Cust customizable for almos an smadels to track inventory the user to perform tasks quickly and the user works with large and multiple analysis oa higher degree. () Wider Charting Options: Beyond basic charts, MS Excel offers a wide variety of chart types and ‘customization options. The user can construct interactive charts with features such as sparklines, jons within a range List of MS Excel Keyboard S 1g important trends a (0. VBA (Vin Bair Application an shin MS Excel Ths allows ues oe ‘ecedivoughaseqee ofcommands BUSINESS ANALY Tigg ‘Mowe fo the reuse! Te Move wthe nent sheet DATA ANALYTICS USING SPREADSHEET ‘AutoComplete partly typed formula ‘Gul +A (Repeat shortcut if only curent region g] | 52 __| Lookup funtion enzuments selected) (Other Useful Shortcuts Cul +F/ Col +H 33__| Addhe curent date anywhere [eut+x St___ | Addthe curent time anywhere 38 7 %6 ‘Open Delete dislg box to delet ow or cok ‘um, or shift cel eft of up ‘Add comments to any cll Hide rows quickly ide columns quickly Reno csi TM BUSINESS ANALY 28 ‘Workbook shortcuts save your workbook on Your comp your progress inthe workbook ‘Close Workbook: The shorteut Ctrl + W al 1 astive WOFkDOOK on yoy sercen. Cove MS Excel apptication: The all workbooks andthe MS Eygy application entirely. General Navigation shortcuts er save around cls worksheet: Use te Left Right, Up and Down arrow Ke 0 avigy around cells within the worksbeet 4. Mone up troagh a eecion af el: The Sf Enter excel shortcut allows YOu o tay tenn an see suf when yoo are working with ge amounts data reed to serll up to find a particular eel '& Jump to top oF bottom of the spreadsheet: Press the CTRL + Up or Down arrow key iy Javigat ote top or bottom of any column in your spreadsheet. 4, Jump from left to ight ofthe spreadsheet: Press the CTRL + Left or Right arrow key y ‘navigate to the left or right side of the spreadsheet along any row. 10, Move tothe beginning ofthe worksheet: Use the shortcut Cr+ Home to jump othe beginning ofthe active worksheet 11, Move to the last used the worksheet: Use the shortcut 1+ End to jump to the last 1, Spend oe eres rooms ri aged aps v_Ta Denes o l asis ie ewh Inert es i i Shi FH neato icky ine ae set you care sen Hove ck esate mo hes cre ig RnB bribes creme ore dn og | 1S Moe pean sts Cts Page Monet the net he: Use snc Ctet + Pape Dom you may find yourself working in a worksheet n. To save time, simply use the Page Up! the previous worksheet tothe next worksheet. DATA ANALYTICS USING SPREADSHEET 29 ultiple worksheets, shift + Page ly, you may use ‘the current sheet and the next sheets in the ait a cell To edit the contents of a cell simply press the key F2 to enter a cell. This key is useful when you only wish to edit the contents of a cell without affecting the formating ofthe cel tell. 20, The shortcut Ctrl +A al 19. the cell inthe worksheet na single go. Sometimes, in range; in such a case, repeat the shortcut 10 hi __ Search the spreadsheet or replace data: Use either Ctrl + F or Ctr+ H shortcut to open the Find and Replace dialog box where you can specify whether you want to search the current sheet or the entire workbook, if you need to match case and to replace the information once or throughout the spreadsheet, Whereas the former shortcut opens the dialog box with an active latte shortcut opens the dialog box with an active Replace tab. ‘Cut selected cells: The shortcut Ctrl+ X enables you to cut an entire cell (inching its format) from its existing location. This nifty shortcut is useful if you wish to shift ace with ts contents and format to different location within the same worksheet or workbook or even a different workbook. Copy selected eels: Use the shortcut Ctrl+C to copy an entire cel You can then paste the cel ‘pith te contents and formatting in any other location within the same worksheet or workbook, ‘or a different workbook, or even a different application, Paste selected cells: The shortcut Ctrl + V may be used to paste an already cut or copied cell toa new location Choose how to paste information: If y 23 sed to paste data cut or copied from an external ing up the Paste Special dialog box, where a in the worksheet. For example, you may Jbove: Have you ever needed to repeat the same information in > By pressing CTRL and D keys together, you can quickly ick inthe ds — BUSINESS ANALY 9, DATA ANALYTICS USING SPREADSHEET 29, Redoto undone action. This shortcut ‘30. Delete cell content: Press the Delete that this shorcut only deletes the as cut oF Copy eet, All you must do ‘he Pomula Buller dialog box whee you can ook upa speci function nd guy ado your set. 51, Execute AutoSu Format cells: 432, Quick formatting: The Excel shortcut Ctl ~ 1 opens the Format cll dialog box that provides options to format cell values font, alignment, border et. ‘38. Apply Date format: Use the shortcut Cul + Shift + 3 to apply the def ‘34, Apply Currency format: The shortcut Ctrl + Shift + $ applies the de fe formato acell, 52. rency format tp intyping 153, Look up funetion arguments: No matter how often you use Excel, there are times when you cannot remember exactly how to write a specific function. Enter ~function, then CTRL ~ A ‘excel shortcut in a cell to look up any function arguments and lear more about the function. Other Useful Shorteuts ly ; 54. Add a date stamp anywhere: If you are updating a soreadsbe thecal reglar schedule, you probably know that entering the date 40. Apply Middle Alignment: Pres my middle ofthe cel 55. + shorteut to add timestamp in the active Fe Geaenan 56. jou to preview how the worksheet will ook shortcut AK +H, then As ser Print preview mode. You can then ‘easily make a 1 workbook prints out exactly as you want i. Create, edit, run and delete macros: If you have used Excel for any length of time, you would ‘xcel Macros, They are one of Excel's most powerful features that allow you to ng, editing running and deleting your macros vet: Use the shortcut Alt +H, then A +€, the shortcut Alt +H, then AC, tol s Use the shortcut Alt + H, then A + R, to al BUSINESS, ANA, Delete a row or a column that allows you to delete the 1. Add comments tan ead to leave them 860 email or messing up the Hide rows quickly: Soret anger yr tan excel sborcut instead of manvaly selecting tows, right-clicking them, ay 0), value, while ignoring hidden rows, error values, nested subtotal —__| also a required argument; the user specifies a numeric value that determines which ore in the evaluation range forthe function. intended to find the 2 largest ‘aggregate functions, within the range AT:A100. the rangeAD:AIOQ. SC _________ Option Behaviour 1 or omited Tor reed SUBTOTAL and AGGREGATE his | Tgnore hidden rows, nested SUBTOTAL and AGGREGATE functions Tgnoreeror values, nested SUBTOTAL and AGGREGATE fnctons Tgnore hidden rows, eor values, nested SUBTOTAL and AGGREGATE functions gnore nothing [gore biden rows Ignore err values | Tore hin ros ander vals il se arguments denote the individual cel references, such as Al or B2, C3:D4, ied function shall operate argument is also a form of cell reference it denotes the range or group of ells on which function shall operate. The following functions require an aay asthe input argument: LARGE(array.k) SMALL(array.k) [Link](array 8) QUARTILE INC([Link]) eee USHEES Ary, ‘DATA ANALYTICS USING SPREADSHEET PeRCENTILEENCIT®N [AGGREGATERAASEI® | Gisine enim wegen mare wemep | —@—] ‘QUARTILEEXCaayi) a =a car AASO) | Pemcam “heh i aga RED By Treas ike eto, um pei ein an ct atebeeACCUL A pay Tables sen wich sy tain bt et nas tog age HO ng Prt te gwelid mein nl ei at nr re The econ ef arp (Re) sams "sth th sen aes The ce faction fp he 7 SMALL then thesegg ge ase Te rm es ee umn rogues an ara ote piesa as A te se = = Seument bere regu Tue : ‘ernst 0 Fang nd summit eb fe ao 1 naVALUEt Oi 8 ‘Funr em en svtch between owing tl ft coer Bo The AGGREGATE fcton Styne nfm Sf eau at prove eens ‘Son verges or ther ggg a (iA song ef eget Wait ee et + To AGOREOAE ani hl area men oust aor + Teich eset a [Link] dO he ag “Te pivotable slows the user to group these ows and clams o he ass of specifi at fis For example, east with ester sales information maybe prouped on he Dass of sect range woud affect the eee causmeieerenee ae oe vine, Bu bing 2 in the etal AMEE _ Exsictitawancentexpittrsome fen [rte oop, yon hts ron maybe cetedto understand tees ens cee ferent locations over ie. sco ‘Example 2; Using te long Das, et ws ply AIEEE paar) [Adding Vales and Calculation ‘After seting up te aroupings, the arly can pity the data variables to be analysed. Such ta variables ae typically numeric dat els Sch sales, invemory evel et “The pivot tbl provides variow aggreption nein such sum, average, cous minimum, ‘manimum. The analyst may chaos he function that best suits tbe analysis. For instance the analyst may wish 1 oe the tot sles per iy foreach of he products, n whic ase the SUM function fost pproprite. Ifthe foes of the anal ison he verge seo es of « “AVERAGE function neds tobe appli the main body ofthe pvt able jocs [ets [es | @ 8 Inorer tundra te dynamics of ring Pot be, et ake he ling example: — wee 3 Example 2.2: You are given the following information regarding the sale of products, belonging to pe 2 [ewe |e | titre tgs, at esa ier ces 4s fe | es |e : a Solin: ping -AGGREGATE) fn a aos an we tte ut ner fastening panies Pema a 23 oe acs [ts Fr Docrnie te on waa) [ae pats foting _snes_—_—[ fasemee js et [cron Mobis | — Sano] | SOGREGATE GAR) | Cian de Wap a wie pming ww] se bets [ting ot i interes . ‘Debi acer: [oes ‘sox SGUGREEATE GEA. Wt ea TALE pe Tbs NGGRTGATE cemetary [ets x ex sce p onksnSMALL) pee SHH queers

You might also like