Quiz Maker IT App PDF

**Module 3.2** **Intermediate Spreadsheet for Accountants** - Working with multiple worksheets in one workbook - Working with multiple worksheets in multiple workbooks **Working with multiple worksheets** **To add a worksheet to a workbook** - Click the new sheet button at the bottom of...

**Module 3.2** **Intermediate Spreadsheet for Accountants** - Working with multiple worksheets in one workbook - Working with multiple worksheets in multiple workbooks **Working with multiple worksheets** **To add a worksheet to a workbook** - Click the new sheet button at the bottom of the window to add a new worksheet to a workbook **To copy and paste from one worksheet to another** - Click the sheet tab for the sheet containing the data to copy - Click the select all button to select the entire worksheet and then click the copy button to copy the contents of the worksheet - Press enter to copy the data from the office clipboard to the selected sheet **To copy a worksheet using a shortcut menu** - Right-click the desired sheet tab to display the shortcut menu - Click "move or copy" to display the move or copy dialog box - Click the desired location and then click to place a check mark in the "create a copy" check box - Click OK to add a copy of the worksheet to the workbook. **To copy a worksheet using CTRL** - Select a sheet - CTRL + drag the selected sheet tab to a location to the right of the other sheet tabs. Do not release the drag. - Release the drag to create the worksheet copy. **To drill an entry through worksheets** - Right-click sheet1 and then click "select all sheets" - Type the entry in the desired cell and then press the DOWN ARROW key to **change sample data to the actual value** - Enter the remaining entry changes in the other cells and then select a blank cell to select the same cell in all of the selected workbooks - Right-click the Sheet1 tab and then click ungroup sheets - Click through the sheet tabs in use to verify that all are identical **Referencing cells across worksheets** **To enter a 3-D reference** - Select the desired cell and then click the AutoSum button to display the SUM function - Click the desired sheet tab to display the worksheet, and then click the same cell to select **the first portion of the argument for the SUM function** - SHIFT + click a new desired sheet tab to select **the ending range of the argument for the SUM function** - Click the enter box in the formula bar to enter the SUM function with the 3-D references in the selected cell **To use the paste gallery** - With the desired cell active, click the copy button to copy the selected cell to the office clipboard - Select the desired range and then click the paste arrow to display the paste gallery - Click the formulas button in the paste gallery to copy the SUM function to the desired range replicating the 3-D references - Press ESC to clear the marquee **Creating separate files from worksheets** **To create a separate file from a worksheet** - Right-click on the desired sheet and then click "move or copy" on the shortcut menu to display the move or copy dialog box - Click the to book button to display the choices - Click (new book) in the list to create a new workbook - Click the "create a copy" check box to ensure it displays a check mark - Click OK to create the new workbook **Module 3.3** **Intermediate Spreadsheet for Accountants** - Creating, sorting, and querying tables **Creating a table** **To format a range as a table** - Select the range to format - Click the "format as table" button to display the format as table gallery - Click the desired table style **To name the table** - Click anywhere in the table and then display the table tools design tab - Click the table name text box end enter the desired table name **To remove duplicates** - Click the remove duplicates button to display the remove duplicates dialog box - Click the select all button to select all columns - Click OK to remove duplicate records from the table - Click OK to finish the process **To enter new rows and records into a table** - Select the desired cell - Type in the information **To center across selection** - Select the desired range. Right-slick to display the shortcut menu - Click format cells on the shortcut menu to display the format cells dialog box - Click the alignment tab and then click the horizontal button in the text alignment area - Click "center across selection" in the horizontal list to center the title across the selection - Click OK to apply the settings **Using a lookup table** **To create a table array area** - Select the range. Right-click the selection and then click format cells on the shortcut menu to display the format cells dialog box - Click the alignment tab and then click the horizontal button - Click "center across selection." Click OK - Click the format painter button and then drag through the desired cells to copy the format of the selected cell to the column headings **To use the VLOOKUP function** - With the desired cell selected, type the VLOOKUP function - Ex: = vlookup (f9, \$1\$3:\$m\$6, 2) **Adding calculated fields to the table** **To create calculated fields** - Click the desired cell - Click the "accounting number format" button so that data in the selected column is displayed as a dollar amount with two decimal places - Double-click specialty to select the field to use for the IF function - Type = IF (\[Specialty\]= "loans", \[Account Values\] \*.0025, 0) to complete the structured reference and then click the enter button to create the calculated column **Conditional Formatting** **To add a conditional formatting rule with an icon set** - Select the range to contain the conditional formatting - Click the conditional formatting button to display the conditional formatting gallery - Click new rule in the conditional formatting gallery to display the new formatting rule dialog box - Click the format style button to display the format style list - Click icon sets in the format style list to display the icon style area - Click the icon style arrow to display the icon style list and then click the desired icon style - Enter the desired values for each icon in the value box - Click OK to display icons in each row of the table **Working with tables in excel** **To insert a total row** - Click anywhere in the table and then display the table tools design tab - Click the total row check box to display the total row and display the sum in the last column of the table - Click the arrow on the right side of the cell to display a list of available function. Select the sum function for the selected cell in the total row. **Sorting a table** **To sort ascending** - Click a cell in the column to be sorted, and then click the sort & filter button to display the sort & filter menu - Click "sort A to Z" to sort the table in ascending order by the selected field **To sort descending** - Click a cell in the column to be sorted and display the DATA tab - Click the "sort largest to smallest" button to sort the table in descending sequence by the selected field **To custom sort a table** - With a cell in the table active, click the "sort & filter" button on to display the sort & filter menu - Click custom sort on the sort & filter menu to display the sort dialog box - Click the "column sort by" button to display the field names in the table - Click the first field on which to sort to select the first sort level - Select the desired options for sort on and order - Click the add level button to ask a second sort level, and then repeat the previous two steps - Click OK to sort the table **Querying a table using autofilter** **To sort a table using autofilter** - Click the filter button in the desired column to display the filter menu - Click "sort smallest to largest" on the filter menu to sort the table in ascending sequence by the selected field. **To query a table using autofilter** - Click the filter button to display the filter menu for the desired column - Remove the check marks next to the fields you wish to hide - Click OK to apply the autofilter criterion **To remove filters** - Display the DATA tab - Click the clear button to display all of the records in the table **To search a table using autofilter** - Click the filter button in the desired column to display the filter menu - Click the search box, and then type the desired search string - Click OK to perform the search **To enter custom criteria using autofilter** - Click the filter button in the desired cell to display the filter menu - Point to number filters to display the number filters submenu - Click custom filter to display the custom autofilter dialog box - Select the desired options for the autofilter - Click OK to display records in the table that match the custom autofilter criteria **To turn off autofilter** - Click the filter button to hide the filter buttons in the table - Click the filter button again to show the filter buttons in the table **Using criteria and extract ranges** **To query using a criteria range** - Enter the criteria data in the desired cells - Click the table to make it active - Click the advanced button to display the advanced filter dialog box - Click OK to hide all records that do not meet the comparison criteria **To extract records** - Click the table to make it active - Click the advanced button to display the advanced filter dialog box - Click "copy to another location" in the action area to cause the records that meet the criteria to be copied to a different location on the worksheet - Click OK to copy any records that meet the comparison criteria in the criteria range from the table to the extract range **Using database functions** **To use the DAVERAGE and DCOUNT database functions** - With the desired cell selected, type the DAVERAGE function or DCOUNT function - Ex: = DAVERAGE (a8:i11, "Supervisor Review", o12:o13) - Ex: = DCOUNT (a8:i22, "Supervisor Review" m2:m3)  **Using the SUMIF, COUNTIF** **To use the SUMIF function** - With the desired cell selected, type the SUMIF function - Ex: = sumif (d9:d22, "Checking/Savings", e9:e22) **To use the COUNTIF functions** - With the desired cell selected, type the COUNTIF function - Ex: = countif (d9:d22, "Loans") **Summarizing data** **To convert a table to a range** - Right-click anywhere in the table and point to table on the shortcut menu to display the table submenu - Click "convert to range" to display a Microsoft excel dialog box - Click the yes button to convert a table to a range **Module 4.1** **Advanced Spreadsheet for Accountants** - Creating a template, importing data, inserting images and smartArts **Creating templates** **To save the template** - Click the Save button to display the Save As screen - Type the desired file name in the File name box - Click the "Save as type" arrow and then click Excel Template in the list to specify that this workbook should be saved as a template - Navigate to the desired save location - Click Save to save the template **To open a template-based file and save it as a workbook** - With Excel active, click File Explorer button on the taskbar to start the File Explorer app - Navigate to the location of the file to be opened - Double-click the template file to open a new file based on the template - Click the Save button to display the Save As screen - Type the file name in the File name box and then navigate to your storage location - Click Save to save the file **Importing data** **To import data from a text file** - Click the first cell on the worksheet to contain the imported data - Click Data on the ribbon to display the Data tab - Click the "From Text/CSV" button to display the Import Data dialog box - If necessary, navigate to the location of the Data files to display the files - Double-click the name of the file to display the preview window - Click the Load arrow, then click Load To to display the Import Data dialog box - Click the Exiting worksheet option button to place the data in the current worksheet rather than on a new sheet - Click OK to import the data **To format the CSV data** - Display the Table Tools Design tab - Click to remove the check mark in the Banded Rows check box - Click to remove the check mark in the Header Row check box to display a Microsoft Excel dialog box - Click the Yes button to remove the header row - Delete the desired row - If needed, adjust the column widths **To use the trim function** - Select the desired cell, type =trim(cell\#) and then click Enter button to trim the spaces from the data in the (cell) and display it in the desired cell **To paste values only** - With the desired range selected press CTRL+C to copy the data - Right-click one of the cells to display the shortcut menu - In the Paste Options area, click the Values icon to paste only the values - Click the save button on the Quick Access toolbar to save the file with the new data **To import data from an access table** - Click the first cell on the worksheet to contain the imported data - Click the Get Data button to display the Get Data menu - Point to the From Database command to display the submenu - Click "From Microsoft Access Database" to display the Import Data dialog box - Navigate to the location of Data Files - Double-click the desired file to display the Navigator dialog box - Click the name of the desired table to display the preview **To delete a column using power query** - In the Navigator dialog box, click the Edit button to display the Power Query Editor window - Click the desired column heading to delete - Click the Remove Columns button to remove the column from the import - Click the "Close & Load" arrow to display the "Close & Load" menu - Click the "Close & Load To" command to close the Power Query Editor Window and to display the Import Data dialog box - Click OK to import the data **To format the access data** - Select a cell, click the Banded Rows check box to remove its check mark - Click the Header Row check box to remove its check mark. When Excel displays a dialog box, click the Yes button. - Delete the desired row - Click the Save button on the Quick Access toolbar to save the file - Close the Queries & Connections pane **To import data from a webpage (1 of 2)** - Click the first cell on the worksheet to contain the imported data - Click the From Web button to display the From Web dialog box - Type the Web page address in the URL box - Click OK to display the Navigator dialog box - Click the table name, then click the Web View tab to look at the data - Click the Edit button to display the Power Query Editor window **To import data from a webpage (2 of 2)** - Make desired selections - Click the "Close & Load" arrow and then click the "Close & Load To" command to close the Power Query Editor Window - When Excel displays the Import Data dialog box, click the Existing worksheet option button. - Click OK to import the data **To copy from word and paste to excel** - Open the file from which the data should be copied. - Select the data and press CTRL+C to copy the contents to the Office clipboard - Close Word and make Excel the active window - Click the desired cell to paste into - Press CTRL+V to paste the data **To transpose columns and rows** - With the range to be copied selected, press CTRL+C to copy the selection to the Office Clipboard - Click a cell to prepare for pasting data into that location - Click the Paste arrow to display the Paste gallery - Click the Transpose button in the Paste gallery to transpose and paste the copied cells **To convert text to columns** - Select the cells to prepare for converting the text to columns - Display the Data tab - Click the "Text to Columns" button to display the Convert Text to Columns Wizard---Step 1 of 3 dialog box - Click the Fixed width option button - Click the Next button to accept a fixed width column and to display the Convert Text to Columns Wizard---Step 2 of 3 dialog box - Click the Finish button to close the dialog box and separate the data **To replicate formulas** - Click the formula(s) to replicate - Drag the fill handle down through the end of the data to replicate the formula **Working with smartart graphics** **To insert a smartact graphic** - Click the "Insert SmartArt Graphic" button to display the Choose a SmartArt Graphic dialog box - Click the desired type of SmartArt in the left pane - Click the desired layout to see a preview of the chart in the preview area - Click OK to insert the desired type of SmartArt graphic in the worksheet - Save this template so it becomes default and no need to create new templates when you need the same kind of formatting/template - After you are satisfied, click "save as" - The original location of the file will pop up - Click excel template - Iwan mo siya as it is dun sa excel template sa document - Rename then save - When u open excel, click template then personal - Its not the same file but different na - If tapos ka na, then save - Pero if issave mo na, magiging excel workbook na yan **IMPORTING DATA** - We have different sources like: - TEXT FILE, COMMA SEPARATED VALUES, HTML, DOWNLOADED FILES FROM INTERNET - Instead of typing one by one, we can import data - Sources of data: excel, CSV (we can open csv already using Microsoft excel. Pero if older version, magagamit mo ang notepad lang), word file, HDML (web document) 1. Microsoft excel - Copy then paste values 2. CSV - If you're gonna open, you open the Microsoft excel of it - Copy paste if Microsoft excel so Madali - Tinawag syang comma separated file kasi the info are separated using commas. - These are treated singularily separated by commas - Most accounting softwares/web documents we downloaded are usually CSV if hindi nakadownload using the latest version of excel Import csv - Go to data tab - Get data - From file - Choose appropriate file - From Text/csv - Then it would ask u to locate where is the file - Click import - After load, may preview - Delimiter is comma. Kapag column isang buong lilitaw kasi pwedeng column ang separator - In our case, we use comma - If load lang clinic mo, it would be added to a new sheet. Na import to a new sheet. Delete first mo ito. - Do everything again. - Instead new sheet, add it to new sheet. - Add to inventory sheet - Then click arrow down tabi ng load - Click load 2 instead of load alone - Click table - Then click on existing worksheet - Then sabihin mo start on A11 - Click okay - It automatically formats as table - How are we gonna remove the formatting? - You can remove header or banded columns etc. - Faster way is: - Click anywhere on the table - Click on range - **Design: convert to range** - So instead of table, magiging normal cells nalang sya - Then remove formatting such as remove fill and delete the headings; u can remove borders - BUT THE THING IS NAIBA ANG FORMAT - Ang product code natin ay naka lower case - Ang original template natin ay naka upper case - Are we gonna type it one by one? Naur - =UPPER(A11) - convert to upper case letters - Right away, nagging upper case na sya. - Use fill handle - Copy them then right click. Paste special then values. - If pinaster mo lang, magreresult to reference kasi natabunan na. so paste values dapat. - =LOWER(A11) -- lowercase naman ito. - Kasi sa MS excel, wala tayo kung ano meron sa MS word - Sentence case kasi is capitalized lang ang unang letter - Meron ito sa MS excel - =PROPER(A11) - We notice na nakaseparate ang type of product and whether its for men or women - Flash fill nalang - Then copy paste delete - =CCONCATENATE (B11,"-",C11) -- from two cells combine to one cell - Copy paste again VALUES - Delete yung "women men..." shift cells to the left - If gusto mo same format, then flash fill then fill formatting only 3. Database - Data: from database - From Microsoft access database - If iba, then select the appropriate - Then locate the file - Then click on import - At this point, wala pa syang preview. Click spetember inventory source 3. - If ur satisfied, click load to then existing worksheet. Then A16 - As u can see, may mga unnecessary like ID - So before importing, we can transform it or remove those unnecessary. - So before loading it, click on transform - A new dialogue box will appear. - So since we don't want ID, REMOVE COLUMN mo siya - And then we want the transaction after product description - So we can click on this one then drag it in the location you wanted it to be - Then click on CLOSE AND LOAD - Make sure hindi *"close and load"* lang kasi it would add in new sheet - Dapat "CLOSE AND LOAD TO" - Same table then existing worksheet then A16 - So it would be imported as new table. - Then select banded columns, header row, etc... - Then yung style nya is blanko lang. - Then adjust mo alignment - Then use the proper function for transactions 4. HTML/online file/web document - Data: get data - Either FROM WEB or FROM ONLINE SERVICES - Or FROM OTHER SOURCES -\> FROM WEB - Then tatanungin niya ang URL - Close mo muna then go to ur desktop - Click mo yung HTML file mo - So copy the URL then go back to your miscrosoft excel - Click FROM WEB - Then CLICK BASIC - Then paste the URL of the HTML file - /// kasi downloaded mo sya from computer - Pero if its from website, then http lalabas - Same dialog box would appear - SEPTEMBER MONITORING ang kailangan mo - LOAD TO - Table - Existing worksheet - However there are extra characters - We can remove the style, remove filter button - **Design: convert to range** - To remove unknown symbols, just delete those - Or what you can do, left right meet function - So we can use the right function - =RIGHT (B21,9) -- returns the specified number of characters from the end of a text string - Count mo ang number of characters from the right (counted ang space) - If canopy mo to next cell, kulang ng S and H kasi men previously eh, women na ngayon A screenshot of a video Description automatically generated - =MID (B25, 9, 9) -returns the characters form the middle of a text string, given a starting position and length) - (magstart ang bilang sa gitna) - What if there are spaces - = TRIM (B21)-- remove all spaces from a text string except to single spaces between words - Aalising ang spaces before and after text - So wala na spaces sad ulo 5. MICROSOFT WORD FILE - We can simply copy paste - New sheet mo ipapaste - The thing is, iba ang formatting nya in a sense na meron tayong column headers sa orginal file. Pero sa ms file, ang header ay product codes. - Tas nagging row header nalang sya - What we can do Is to copy again then PASTE SPECIAL - Marky ang tawag sa running lines - Clipboard: paste special (control + alt + V) - Click transpose - Then okay - Then copy paste again to original sheet - Then this time values - Kaso may unnecessary nanaman - Hindi nasunod ang actual formatting. Simply delete on these one or pwede ka mag FLASH FILL - Then sa transation ay pwedeng gamitin ang =LEFT (C26,5) - Or pwede ring flash fill - Remove excess "S" sa sales - Insert: pictures - Insert: screenshot then click on SCREEN CLIPPING - Automatic naadd na sya - Insert: smart art -\> this can be used for presentations for graphics pero di naman naggamit toh - You can select pyramid, hierarchy etc