SOLUTION: AutoFill formulas | My Assignment Tutor

Seminar Week 6Adapted by Colin Fu Assignment 1• Have you started your assignment 1?• Go watch the videos • More videos in Week 8 Revision• It is good that you go watch them nowrather than waiting till Week 8?Assignment 1 3 Excel editing features• Excel has various features intended to facilitate data inputand spreadsheet creation:– Autofit– Autofill– Autoformat• Note: Tutor demonstrate the following on Excel… Column Width, Row Height– Autofit, Drag, Diaglog AutofillIt is entirely boring to type in the same data over and over. And it certainly isnot interesting to number lines 1, 2, 3, … or fill in months of the year or days ofthe week. Excel’s AutoComplete and AutoFill features are just the thing tohandle these repetitious actions! AutofillYou use AutoFill to:– copy data to other cells in the row or column– continue a pattern for dataTo use AutoFill, you just select a cell or cells and hover over the bottom right corner of theselection until the mouse pointer changes to the AutoFill shape, a black cross . Drag the fillhandle over the cells you want to fill – either down a column or across a row. Autofill AutofillAutoFill formulasWhen you use AutoFill to copy the formula across the rows (columns), Excel automaticallychanges the cell references to use the new rows (column). Absolute Reference & RelativeReference AutoCompleteExcel keeps a list of what you have already entered in each column.When you start entering data in a new cell, Excel may offer tocomplete your typing for you. For example, if you are entering a list ofnames and addresses, it is likely that many of them will have thesame city and state and even the same street. It can save a lot oftyping if you let Excel finish out the ones that repeat.To accept Excel suggestion, just make another cell active by doingone of these actions:• Press ENTER or Press TAB or Press an arrow key or Click in adifferent cellNote: Excel will not make a suggestion unless it sees only onematching choice in the list of things you have already typed in thatcolumn. Auto Format• Formatting makes a worksheet look good.• The choice of background colour, font style, fontsize, and other formatting options makes data easier toanalyze.• It also makes important information easier to see,while giving the spreadsheet a professional look.• One way to simplify the job of formatting a worksheet inExcel is to use the AutoFormat option. Auto-Format• Let’s practise Auto Format• The auto format feature is Adding AutoFormatFollow the stepsHere are the steps to add the AutoFormat option to the QAT:– Right-click on any of the existing icons on the QAT.– Click on ‘Customize the Quick Access Toolbar’ option. • • Adding AutoFormat• In the Excel Options dialog box that opens, in the ‘Choosecommands from’ drop down, select All Commands. Adding AutoFormat• In the list of commands, scroll down and select AutoFormat• Click on the Add button.This will add the AutoFormat option to the list of iconsalready available in QAT.• Click OK. Adding AutoFormat• Select the entire data set.• Click on the AutoFormat icon in the Quick AccessToolbar. Using AutoFormat• Type the following into Excel• Leave A1 blank• Follow the formatting (left, right, justified) Using AutoFormatIn the dialog box, you willind 16 different kinds oformatting design. Selecthe one you want to apply.Click OK. Modifying AutoFormat Design• Below are the six types of formats that you canenable/disable when using AutoFormat:– Number Formatting– Border– Font– Patterns– Alignment– Width/Height Modifying AutoFormat Design• Click on the AutoFormat option in the QAT. Modifying AutoFormat Design• In the dialog box, click on the ‘Options’ button. Modifying AutoFormat Design• Select/deselect the formatting design options Removing the FormattingRemove individual elements using the inbuilt options.– For example, if you only want to remove the borders, usethe borders options to remove it.Remove the entire formatting.– You can do this by selectingthe last design in theAutoformat dialog box(as shown on the right). • • Tables – Inserting a Table• Use the attached Excel file, and follow the instructions asgiven next Tables – Inserting a Table Tables – Inserting a Table Results Sort a Table ilter a Table Display Total Row Display Total Row Format as Table• Another Tableformating feature• It is similar toAutoFormat• Automaticcolouring of tables Format as Table result Useful featuresUpper left corner:mark the whole tableDrag down thesplitter to see twoparts of the table atthe same time • • olumn/row sizesChange column and rowwidths by dragging ondividing lines on the axesCTRL and double-click onthe axes will auto-sizecolumns and rowsrespectively • • Highlightinging• Mouseclick highlights one cell• CTRL-mouseclick highlights an additional cell• SHIFT-mouseclick highlights all cells from the cursor• Click on the colum/row designator highlights column/row Cell formatting• Right-clicking on a cell or highlighted cells, select „formatcells“ in the context menu– Print appearance (lines, font etc.)– Value formatting– Currencies– Date– Time Comments•Annotations•Red triangle in upper right corner•Mouse over brings up comment Assignment 1• Have you started your assignment 1?• Go watch the videos • More videos in Week 8 Revision• It is good that you go watch them now rather than waitingtill Week 8?Assignment 1 41 • Videos– Watch the this week videos? They are important.– You need to watch next week videos too beforenext session.• Quizzes– You attempted the quizzes?• Activities (Apply and Consolidate)– How about all other activities (Apply andConsolidate)?Have you?ICT 42 • When email, please email your own tutor and mention themodule and time you doing the ICT (we have around 300students)– Babatunde Adetayo Babatunde.Adetayo@uwl.ac.uk(Tuesday, Thursday)– Dr Suzzy Nsiegbe Suzzy.Nsiegbe@uwl.ac.uk (Tuesday)– Dr Elizabeth Scott Elizabeth.Scott@uwl.ac.uk (Thursday• We have around 100 students…• We will try to get back to you in 48 hours. If no reply after 48hours , please kindly email your tutor again copying themodule leader, Dr Colin Fu, Colin.Fu@uwl.ac.ukEmailICT 43