Gr11 CAT Practical LB print 20 9 19 hlayiso.com

Practical Book COMPUTER APPLICATIONS TECHNOLOGY CAPS Gr11_CAT_Practical_LB.indb 1 2019/09/20 11:53 AM

MTN SOUTH AFRICA, THROUGH MTN SA FOUNDATION IS A PROUD SUPPORTER OF CAT AND IT DIGITAL BOOKS. As an organisation rooted in technology, we believe in providing a new bold digital world to communities we operate in. This unique digital book provides the fundamental knowledge necessary for a sound grounding from which to make practical use of the complete and indispensable application-oriented information regarding Computer Applications Technology (CAT) and Information Technology (IT). It is a foundational reference for today’s secondary school learners and teachers alike – as well as for the next generation of CAT and IT students. Computer Applications Technology Grade 11 Practical Book ISBN: 978-1-928388-62-3 First published in 2019 © 2019. Copyright in the text remains with the contributors. Restrictions You may not make copies of this book in part or in full – in printed or electronic or audio or video form – for a profit seeking purpose. Rights of other copyright holders All reasonable efforts have been made to ensure that materials included are not already copyrighted to other entities, or in a small number of cases, to seek permission from and acknowledge copyright holders. In some cases, this may not have been possible. The publishers welcome the opportunity to redress this with any unacknowledged copyright holders. Quality Assurance team for Computer Applications Technology Carina Labuscagne, Claire Smuts, Deidré Mvula, Edward Gentle, Estelle Goosen, Feroza Francis, Hendrik Hahn, Ighsaan Francis, Magdalena Brits, Natasha Moodley, Peter Davidson, Reinet Barnard, Tyran Ferndale and Zainab Karriem Gr11_CAT_Practical_LB.indb 2 2019/09/20 11:53 AM

Contents Term 1 Terms 1, 2, 3 Chapter 1 File Management in Word Processing�� 1 Chapter 6 Styles�� 67 Introduction�� 1 Introduction�� 67 Unit 1.1 File management�� 4 Unit 6.1 Editing or creating styles��68 Properties�� 5 Applying heading and paragraph styles��70 Printing�� 6 Revision activity�� 71 Revision activity�� 13 At the end of the chapter�� 71 At the end of the chapter�� 13 Term 3 Chapter 2 Templates and Input data in Word Processing� 14 Introduction�� 14 Chapter 7 Mailings and References�� 72 Unit 2.1 Templates and styles��15 Introduction�� 72 Templates��15 Unit 7.1 Mailings��73 Unit 2.2 Import/Export data��18 Mail merge letters��73 Revision activity�� 23 Mail merge labels��76 At the end of the chapter�� 24 Unit 7.2 References��81 Table of content��81 Chapter 3 Editing�� 25 Table of figures��84 Introduction�� 25 Footnotes��85 Unit 3.1 Editing��26 Captions��87 Paste special��26 Citations��89 Find and replace��27 Bibliography��92 Revision activity�� 29 Index��93 At the end of the chapter�� 29 Revision activity�� 95 At the end of the chapter�� 95 Term 2 Chapter 8 Spreadsheet Formatting�� 96 Chapter 4 Page and Document Layout�� 30 Introduction�� 96 Introduction�� 30 Formulas in a nutshell�� 96 Unit 4.1 Page layout and design��31 Unit 8.1 Advanced formatting��98 Themes��31 Conditional formatting�� 98 Cover page��33 Unit 8.2 Advanced formulas and functions�� 107 Unit 4.2 Document layout��35 Unit 8.3 Error indications�� 111 Section breaks and sections��35 Circular reference�� 111 Headers and footers��37 #NULL!�� 113 Unit 4.3 Paragraphs��44 Revision activity��114 Columns��52 At the end of the chapter��115 Revision activity�� 56 At the end of the chapter�� 57 Chapter 9 IF Functions and Charts��116 Introduction��116 Chapter 5 Electronic Forms and Online/Offline Help� 58 Unit 9.1 Simple IF function�� 117 Introduction�� 58 SUMIF�� 118 Unit 5.1 Electronic forms��59 COUNTIF�� 120 Unit 5.2 Online/offline help��63 Unit 9.2 Relational operators�� 121 Revision activity�� 65 At the end of the chapter�� 66 Gr11_CAT_Practical_LB.indb 3 2019/09/20 11:53 AM

Unit 9.3 Charts�� 124 Chapter 13 Tables and Queries��183 Creating charts�� 124 Introduction��183 Revision activity��131 Unit 13.1 Design database tables�� 184 At the end of the chapter��131 Basics of good database design�� 184 Unit 13.2 Queries�� 194 Chapter 10 Working with Spreadsheets��132 Design basic queries (AND, OR, NOT, SORT)�� 194 Introduction��132 Revision activity��198 Working with Excel��132 At the end of the chapter��198 Unit 10.1 Import/Export data�� 134 Importing data�� 134 Chapter 14 Reports��199 Exporting data�� 139 Introduction��199 Unit 10.2 Help files�� 142 Unit 14.1 Reports�� 200 Unit 10.3 Manipulating worksheets�� 143 Design basic reports�� 200 Working with sheets�� 143 Different sections of a report�� 200 Print options�� 149 Basic calculations in a report�� 203 Unit 10.4 Integration techniques�� 151 Headers and footers�� 204 Linking cells and formulas between sheets�� 151 Import/Export data�� 206 Revision activity��152 Unit 14.2 Formatting techniques�� 209 At the end of the chapter��153 Unit 14.3 Integration�� 210 Unit 14.4 Troubleshooting�� 211 Chapter 11 Templates and Integration��154 Unit 14.5 Problem solving with databases�� 212 Introduction��154 Unit 14.6 Designing databases for specific purposes�� 213 Recapping headers and footers as well as data types��154 Revision activity��214 Unit 11.1 Templates�� 156 At the end of the chapter��215 Unit 11.2 Integration with other packages�� 159 Chapter 15 Introduction to HTML��216 Unit 11.3 Problem solving�� 160 Unit 11.4 Troubleshooting�� 161 Introduction��216 Troubleshooting spreadsheets�� 161 Unit 15.1 Introduction to HTML�� 218 Excel help files�� 162 What is HTML?�� 218 Revision activity��162 What is an HTML editor?�� 218 At the end of the chapter��163 HTML syntax�� 219 Unit 15.2 Basic HTML tags�� 220 Term 4 Opening tag and closing tag�� 220 Basic document tags�� 220 Chapter 12 Introduction to Databases��164 Heading elements�� 221 Text elements�� 222 Introduction��164 Text formatting�� 223 Databases��165 HTML comments�� 225 Unit 12.1 Basics of Access�� 166 Unit 15.3 Text formatting�� 226 Database uses�� 166 Anatomy of an attribute�� 226 Data types in Access�� 168 Unit 15.4 Putting it all together �� 231 Unit 12.2 Work with different views�� 170 Revision activity��232 Unit 12.3 Database structure�� 172 At the end of the chapter��233 Create tables�� 172 Primary keys�� 174 Chapter 16 Principles of Web Design��234 Field properties�� 174 Introduction��234 Formatting tables�� 175 Unit 16.1 HTML and web design�� 235 Adding and deleting records and fields�� 176 Good website design�� 235 Creating a form�� 178 Unit 16.2 HTML lists, links and images�� 238 Data validation and sorting�� 180 HTML lists�� 238 Revision activity��181 HTML links�� 240 At the end of the chapter��182 HTML images�� 242 Revision activity��244 At the end of the chapter��245 Gr11_CAT_Practical_LB.indb 4 2019/09/20 11:53 AM

Dear learner Welcome to the Practical Book for Computer Applications Technology Grade 11. Please read the information below about the book before you start using it. INTRODUCTION This book provides you with all the information you need to cover the Grade 11 Before getting started, watch Computer Applications Technology curriculum. It will also help you develop the the video in the QR code. skills, attitudes and values you need to be successful in your studies. Each chapter in the book has the following features, which are meant to help you learn: ●● Overview This is an overview of the contents and the units of the chapter. ●● Learning outcomes https://www.youtube.com/ These are the objectives that are addressed in the chapter. It states what watch?v=nKIu9yen5nc you should be able to do by the end of the chapter and gives you an idea of what will be expected of you as a learner. After completing each chapter, ask yourself, ‘Am I able to do everything stated in the learning outcomes?’. If not, you should revise the content covered in the chapter. ●● Introduction The introduction is a brief summary of why you will be learning the content that is covered in the chapter. Sometimes it will give you a brief background of how far technology has come, while at other times it will just give you a short summary that introduces the content that will follow in the chapter. ●● Activities These cover questions you have to answer based on the content presented before them. Activities will help you check whether you have understood the content presented in the book or not. ●● ‘Something to know’, ‘Take note’ and ‘Tip’ boxes The boxes provide extra, interesting content that might caution you to ‘take note’ of something important; or give you additional information. Note that the content in these boxes will not be part of your exams. ●● QR Codes, Videos and Screen captures These will link you to online content. When you are in the eBook, you can easily access the links. ●● Revision activities This is a revision activity based on what you have covered in the chapter. Take time to answer the questions on your own. You teacher may also use these to assess your performance during class. ●● At the end of the chapter This is a checklist to ensure you understand all the content cover in the chapters. These criteria are linked to the learning outcomes at the beginning of the chapter. Before getting started, watch the video in the QR code in the margin. Gr11_CAT_Practical_LB.indb 5 2019/09/20 11:53 AM

You can use the QR codes on these pages to link to online content for further information on these topics. Chapter 5 Chapter 14 MICROSOFT SUPPORT��63 INTEGRATING ACCESS�� 210 Chapter 10 Chapter 15 MOVE AND COPY CELLS AND THEIR CONTENT�� 145 TO FIND OUT MORE ABOUT THE W3C STANDARDS�� 219 PRINTING IN EXCEL�� 150 Chapter 13 CHARACTERS THAT DEFINE INPUT MASKS�� 190 Gr11_CAT_Practical_LB.indb 6 2019/09/20 11:53 AM

TERM 1 FILE MANAGEMENT IN CHAPTER WORD PROCESSING 1 CHAPTER OVERVIEW Unit 1.1: File management By the end of this chapter, you will be able to: ●● Understand how a file managing application is used. ●● Print a document. ●● Use different print options. ●● Print to PDF. ●● Share a document through email or on the cloud. INTRODUCTION Microsoft Word is the most popular word processing program for creating a wide variety of different text-based documents that can be customised to suit your needs. It is simple to use and is packed with features to help you make good-looking, effective word processing documents. Each new update has brought better and more advanced features to the program, like advanced page layouts and creating a table of contents with a single click. File management is all about organising your computer, deleting unnecessary files and folders as well as sorting files and folders. In Grade 10, you learned how to restore, move and view files and folders on your computer using a proper file management system. Furthermore, you learned about the different file types. This activity will refresh your memory of the work you learned in Grade 10. term 1 I chapter 1 File Management in Word Processing 1 Gr11_CAT_Practical_LB.indb 1 2019/09/20 11:53 AM

Activity 1.1 Do this activity on your own and answer the questions that follow: 1. Briefly explain what folders (directories) are and their role in file management. 2. What does the figure below show an example of? C:\School\CAT\Assignment1.docx 3. What does the C:\ refer to? 4. Draw a diagram to indicate the following folders and subfolders in a structure (use these names to create folders and subfolders): a. You have files for school, gaming (World of Warcraft and DotA), music, other (Random files) and personal. 5. Describe how the files on a computer can be organised to make it easier to find the appropriate files. 6. Fill in the following table based on these file paths: a. c:\Games\WorldofWarcraft\WoW.exe b. e:\Videos\Comedy\Game night.mp4 c. c:\Program Files (x86)\Calculator.exe # DRIVE LETTER FOLDER SUBFOLDER FILE NAME EXTENSION a. b. c. 7. Match the term in Column A with the correct description in Column B. COLUMN A COLUMN B 7.1 Text files A This identifies the type of file and how the computer should interpret it. 7.2 .gif B In Microsoft Windows, you will find deleted files here. 7.3 Archives C This is used to store plain text. 7.4 Folder D Animation files are files that interpret moving images 7.5 File extension E You use this to paste files. 7.6 Recycle bin F These are the type of files that can combine and compress multiple different files into one file. 7.7 CTRL+V G You use this to cut files. 7.8 CTRL+X H Another word for ‘directory’. 8. List ONE difference between a .txt and .docx file. … continued 2 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 2 2019/09/20 11:53 AM

Activity 1.1 … continued 9. You created a word-processing document in Word for your father and saved it as Organogram. When he wanted to open it, Windows displayed the following message (refer to the screenshot below): When you examined the file, you found that it was saved as follows: a. Explain the importance of file extensions by referring to the scenario above. b. Explain one way in which the problem can be solved. c. List two ways screenshots can be taken on a computer. 10. Give TWO advantages of file compression. term 1 I chapter 1 File Management in Word Processing 3 Gr11_CAT_Practical_LB.indb 3 2019/09/20 11:53 AM

UNIT 1.1 File management A file manager is a built-in application that is used to organise files and folders. To open the file manager, double select the This PC icon. The This PC folder will open in a new window. From here, you can start working with your files and folders. When working with a file manager, take note of the following: ●● File names should be short. ●● File and folder names should indicate the contents. ●● File extensions determine the type of program that is used to open that specific file. Managing your files is all done by selecting File in the top left-hand corner of a Word document, which opens the File menu: Figure 1.1: Word 2016 File menu In this menu, you can change or do variety of things with the options here, including change or view properties, set printing options, send the document as either an email, PDF or Take note internet fax, and convert the document to different formats. Note that in all the In this section, you will learn how to use the File menu, or Backstage View, to view and examples in the change properties, print and export, send to and share word-processing documents. subsections below, all the actions will be done in the File menu in Microsoft Guided activity 1.1 Word. This means that all the steps in the examples Step 1: Open a Word document will assume you have an Step 2: Select File, Info. open Word document and that you have selected File to open the menu. Step 3: This will open the Backstage View that you will use in this section. 4 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 4 2019/09/20 11:53 AM

PROPERTIES The file properties window shows you a range of information about a file. There you can see the name of the document, given protection, inspection and management options. You can also see all the document properties, including dates on when it was last printed, the last time it was modified and when it was created. You will be able to see who has made changes and who created the document. The Properties menu can also give you a link to the file’s location on your computer’s hard drive. You can access the Properties menu in Microsoft Word to see the properties of word processing documents. Guided activity 1.2 Step 1: Select Info to open the Info page. Step 2: Select the arrow next to properties. Step 3: Select Advanced Properties. Figure 1.2: Advanced properties term 1 I chapter 1 File Management in Word Processing I Unit 1.1 File management 5 Gr11_CAT_Practical_LB.indb 5 2019/09/20 11:53 AM

Activity 1.2 1. Open Act1.2(CAT).docx a. Change the author of the document to your own name and surname. b. Insert a title for the document in capital letters. Bold the title. c. Write a few sentences about why you chose CAT as a subject. d. Change the font to Bookman old style, font size 16. e. Save the document. 2. Open the Act1.2(Answer) word processing document and use the 2Water document to answer the questions that follow. a. How many paragraphs appear in the document? b. What is the first word that appears on line 100 in the document? PRINTING When you want or need to print a Word document, there are a number of things you can do to minimise the amount of paper and ink you use. These can be accessed in the Print menu after you have selected File in the ribbon. Figure 1.3: Print menu You can choose to print the entire document or just selected pages (for example, pages 31– 40), or only print odd or even numbered pages. You can also choose how many copies you want to print, what quality settings to print at and how many pages per sheet to print. You can also choose to print single- or double-sided copies. Guided activity 1.3 Step 1: Select the Print command to open the print settings menu. … continued 6 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 6 2019/09/20 11:53 AM

Guided activity 1.3 … continued Step 2: Select the arrow next to Print All Pages. Step 3: Change the setting to Custom Print. Step 4: Enter the page range in the box the page settings. Step 5: Select the arrow next to Print One Sided. Step 6: Select Manually Print on Both Sides. Step 7: Select Print. term 1 I chapter 1 File Management in Word Processing I Unit 1.1 File management 7 Gr11_CAT_Practical_LB.indb 7 2019/09/20 11:53 AM

Guided activity 1.4 Step 1: Select the Print command to open the print settings menu. Step 2: Select the arrow next to Print All Pages. Step 3: Select Only Print Odd Pages or Only Print Even Pages. Step 4: Select Print. To print more than one copy of a document, change the number in the Copies box next to the Print button. Figure 1.4: Number of copies 8 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 8 2019/09/20 11:53 AM

To change the quality of the printing, select Printer Properties below the name of the printer. Select the colour: full colour or black. By selecting Economode or Draft mode, ink or toner is saved, as the printer will print in a lower quality. Lastly, you can choose how many pages you want to print on a sheet by selecting the arrow next to 1 Page Per Sheet and choosing how many pages should be printed on a sheet of paper. Something to know Figure 1.5: Number of pages per sheet Always make sure to select black if you want the printer EXPORT/PRINT TO FILE to use ONLY the black You can also print your Word document to PDF. A PDF is a Portable Document Format file cartridge to print. This is which means that it cannot be changed, but only viewed on the computer. Instead of because the printer will still use some of the ink or toner producing a printed copy of your document (a hard-copy) Word converts the document to from the colour cartridges to a PDF. print in grayscale. Guided activity 1.5 Step 1: Select the Print command to open the print settings menu. Step 2: Change your printer to Microsoft Print to PDF by selecting the arrow next to the printer name. … continued term 1 I chapter 1 File Management in Word Processing I Unit 1.1 File management 9 Gr11_CAT_Practical_LB.indb 9 2019/09/20 11:53 AM

Guided activity 1.5 … continued Step 3: Select Print. Step 4: Choose where to save your PDF in the Print to file dialogue box and give your file a name. Step 5: Select OK. Saving different Word documents You can save your Word document in different file formats. The different file types are: ●● Document saves the file as *.docx and uses the Word document format. ●● Word 91-2003 Document saves the file as *.doc. This eliminates compatibility issues when sharing with people who have older versions of Word. ●● OpenDocument Text saves the file as *.odt. This format is compatible with Word and open source applications like OpenOffice and LibreOffice, but you might see formatting differences and some Word features aren’t available in .odt files. ●● Template saves the file as *.dotx. You can set up styles in a template for different types of documents (more on that later) and then use the template to apply those styles to a document you created. With a template, there is no need to reformat the document every time you create a report, for example. 10 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 10 2019/09/20 11:53 AM

●● Plain text saves the file as *.txt. This format only keeps the text, no images or formatting at all. This also saves space on your hard drive as it can reduce the size of a document substantially. ●● Rich Text Format saves the file as *.rtf. RTF files keep the formatting information and images but can be opened on other word processing platforms. ●● Single File Web Page saves the file as *.mht/*.mhtml. This is a web archive file that holds HTML files, images, animation, audio and other media content. You can also press F12 on your keyboard to open the Save As menu and choose the file type from the drop-down menu. Something to know Note that when you save or print a document to PDF the original document is not replaced by the PDF. The original file will still be Figure 1.6: Saving different Word documents available to be edited. SEND TO/SHARE To share a word processing document, you can choose to send it as an email attachment. When you send a document as an email attachment, you will send a copy of the document to everyone you choose to email. A word processing document can also be shared to the Cloud. OneDrive, Google Drive and Dropbox are examples of cloud storage. term 1 I chapter 1 File Management in Word Processing I Unit 1.1 File management 11 Gr11_CAT_Practical_LB.indb 11 2019/09/20 11:53 AM

You can share a word processing document using any email program, you just need to attach the file. To do this using Gmail, follow the steps in the example below. Guided activity 1.6 Step 1: In the email window, select the paper clip icon (this indicates an attachment). Step 2: Select the document you want to send and select Open. Step 3: Once the document has been uploaded, you can select Send. Activity 1.3 1. Open the PrintToPdf.docx document. a. Save this document as a PDF, using the same file name. b. Close the word processing document. c. Attach the PDF file to an email and send it to your teacher. 12 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 12 2019/09/20 11:53 AM

REVISION ACTIVITY 1. The secretary has created the following folder structure on her computer’s hard drive, but she needs some help in organising the files and folders. Use the DATA folder for this question. a. Select ALL the files of 2KB in the 2007 folder, and then delete them. (2) b. Move the Archive folder so that it becomes a subfolder of the 2007 folder. (2) 2. Open the Departures word processing document that is stored in the Tour Transport folder. a. Change the author property of the document to your name and surname. (2) b. Make sure the file is Read Only so that it cannot be changed. (2) 3. You have been asked to create a printable advert for a new shop opening. There is a women’s and children’s section. (22) a. Create the advert on a blank word processing document using appropriate alignments, fonts and sizes, tab stops, margins and layout. b. After you have created this advert, save it as a PDF in the Data folder as Revision Activity PDF. You will need: ●● A logo ●● Name for the shop ●● At least three items per section with clear pricing ●● The different sections (women’s and children’s) ●● Double-line page border ●● Margin of 1 cm all round ●● Portrait orientation ●● Images ●● Two pages long TOTAL: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use file manager on the computer? 2 Print a document? 3 Print a document using a range of pages? 4 Change the number of copies before printing a document? 5 Use print to PDF? 6 Send a document using email or the cloud? term 1 I chapter 1 File Management in Word Processing I Unit 1.1 File management 13 Gr11_CAT_Practical_LB.indb 13 2019/09/20 11:53 AM

TERM 1 CHAPTER TEMPLATES AND INPUT DATA IN 2 WORD PROCESSING CHAPTER OVERVIEW Unit 2.1: Templates and styles Unit 2.2: Import/export data By the end of this chapter, you will be able to: ●● Use built-in templates. ●● Create templates from existing documents. ●● Link objects in a word processor. ●● Import data from other file formats. INTRODUCTION Templates are sample documents that already have some details in place. These details can be changed by the user. The user can then use the template to create, edit and manipulate the document and then save it as a normal word processing document. 14 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 14 2019/09/20 11:53 AM

UNIT 2.1 Templates and styles Templates save the time and effort it takes to set up and style general documents, such as CVs and reports, since they only need to be set up once to be used over and over again. Styles, on the other hand, are predefined ways of formatting text elements in a document, including giving headings specific numbers, font sizes, colours and line spacing. Microsoft Word comes with a number of pre-built templates for a variety of different types of documents, but it also allows you to create a template of your own with your own styles and document formatting. In this unit, you will learn how to use pre-built templates in word processing software and how to create your own templates. You will also learn how to change, edit and create styles in a word processing document. TEMPLATES A template is a document that is formatted to a specific style. You can change the font type, size and colour using the formatting tools that you have learned about in Grade 10. It also contains fields that you can fill in. These fields are labelled with the information they should contain, which makes creating the document even easier. CREATING YOUR OWN TEMPLATE You can create a template from a word processing document. To save a document as a template, refer to the example below. Guided activity 2.1 Step 1: Open a blank document and create a document to be used as a template. Select File and then Save As. … continued term 1 I Chapter 2 Templates and Input data in Word Processing I Unit 2.1 Templates and styles 15 Gr11_CAT_Practical_LB.indb 15 2019/09/20 11:53 AM

Guided activity 2.1 … continued Step 2: Double-click Computer or This PC. Step 3: Type a name for your template in the File name box. Step 4: For a basic template, select the template item in the Save as type list. In Word for example, select Word Template. Step 5: Browse your computer to find the folder you want to save your template to. Step 6: Select Save. 16 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 16 2019/09/20 11:53 AM

Activity 2.1 1. Create a new template. a. Open any one of the in-built letter templates. b. Change the ‘Normal’ text style to: Colour: Red, Text size: 11 pt. c. Save the document as MyTemplate and close the document. 2. Convert a document to a template. a. Open Act2.1(Statement).docx. b. Next to ‘Bill To’ section, add a field that will automatically insert the user name of the person using the template. c. At the ‘Date’ section, modify the field so that it automatically inserts the current date. c. Save and close the document. 3. Create your own template. a. Open a blank document. b. Create your CV. c. Create at least one heading style and one font style that you feel will enhance your CV. d. Save and close the document. term 1 I Chapter 2 Templates and Input data in Word Processing I Unit 2.1 Templates and styles 17 Gr11_CAT_Practical_LB.indb 17 2019/09/20 11:53 AM

UNIT 2.2 Import/Export data You can import data from other file formats or even entire files into a word processing document. This will insert the entire content of the file into the document you are working on. You can also use copy and paste to do this, but by using the importing method you will be sure that you do not miss any text to copy. To import data, follow the steps in the following guided activity. Guided activity 2.2 Step 1: Open only the document that you want the data to be copied to. Place your cursor in this document where you want the data be inserted. Step 2: Select the Insert tab. Step 3: In the Text group, select Object. Step 4: Select Text from File. Step 5: Choose the file you want to import data from in the pop-up window that appears. Step 6: Select Insert. Activity 2.2 1. Import data from a text file. a. Open a new blank Word document. b. Import the data from the Example_Data text file. Make sure that the text is unformatted. … continued 18 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 18 2019/09/20 11:53 AM

Activity 2.2 … continued c. Convert this data into a table (see example below). d. Save the document as Example_Data.docx and close the document. 2. Import data from a Word file. a. Open a new blank Word document. b. Import the data from 2Trade_Handel as a link. c. Insert a page break. Then on the next page import the data from 1Water. d. Insert a page break. Then import the data from 2Trade_Handel again, but not as a link. e. Save the document as Trade_Handel(imported) and close the document. You can import tables and other objects from a spreadsheet into a word processing document. Guided activity 2.3 Step 1: Place your cursor in the document where you want to insert the data. Step 2: Select the Insert tab. Step 3: In the Text group, select Object. Step 4: Select Object. … continued term 1 I Chapter 2 Templates and Input data in Word Processing I Unit 2.2 Import/Export data 19 Gr11_CAT_Practical_LB.indb 19 2019/09/20 11:53 AM

Guided activity 2.3 … continued Step 5: Select Create from File. Step 6: Select Browse to select the file you want to import. Step 7: Select the file you want to import and then select Insert. Step 8: Select Link to file if you want to insert the data as an image with a link to the source document. This will ensure that the table in Word will update if you make changes to the original spreadsheet. … continued 20 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 20 2019/09/20 11:53 AM

Guided activity 2.3 … continued Step 9: Wait until the data has been imported. You should see something like this: Activity 2.3 1. Import data from an Excel document. a. Open a blank Word document. b. Import the data from the 2Det_Bes.xlsx document. Note: Do not import it as a link. c. Insert the following as a header to the document: “Grade 11: Name and Surname”. d. Next to the table, re-import the 2Det_Bes.xlsx document as an icon. e. Insert a page break. Then import the data from 2Det_Bes.xlsx again. This time import it as link. f. Save document as Import_spreadsheet.docx and close the document. 2. Use a text file to import data from an Excel document. a. Open a blank Excel document. b. Open the text file Example_Data. c. Select all the data in Example_Data and copy it. d. Select the first cell the Excel document and paste the data you copied from Example_Data. e. Save the Excel document as NewExampleData.xlsx. f. Open a blank Word document. g. Import NewExampleData.xlsx as a link. h. Save the document as NewExampleData.docx and close the document. You can also insert whole files into a word processing document as an object. The benefit of this is that the entire file is saved in the document and can be opened at the same time as the original document, even by other users on other computers. This means that you will not need to send two attachments in an email or make copies of more than one document. It also eliminates the danger of the file path becoming invalid, like when you link to a document. A file path is the route to follow to find a file that has been saved and shows which directory to search in and which folders you must open to find the file again. term 1 I Chapter 2 Templates and Input data in Word Processing I Unit 2.2 Import/Export data 21 Gr11_CAT_Practical_LB.indb 21 2019/09/20 11:53 AM

To do this, follow the steps in Guided activity 2.4. Guided activity 2.4 Step 1: Place your cursor in the document where you want to insert the data. Step 2: Select the Insert tab. Step 3: In the Text group, select Object. Step 4: Select Object. Step 5: Select Create from File. Step 6: Select Browse to select the file you want to import. Step 7: Select the file you want to import and then select Insert. … continued 22 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 22 2019/09/20 11:53 AM

Guided activity 2.4 … continued Step 8: Select Display as icon if you want the file to be imported directly into the Word document. Select OK. Click on the icon to open the file. Step 9: Wait until the file has been created and imported. You should see something like this: REVISION ACTIVITY 1. Open the Chapter 2_Revision Activity word processing document, which contains an overview of the Harvest Food Festival. 1.1 Follow the instructions below to the first page of this document so that it appears similar to the one in the example below. NOTE: The WordArt and changed picture may appear slightly different from that which is shown below. 1.2. Format the heading “HARVEST FOOD FESTIVAL”’ as follows: ●● Apply any WordArt of your choice to the heading. (2) … continued term 1 I Chapter 2 Templates and Input data in Word Processing I Unit 2.2 Import/Export data 23 Gr11_CAT_Practical_LB.indb 23 2019/09/20 11:53 AM

REVISION ACTIVITY … continued ●●Rotate the WordArt by 90 degrees. (2) ●●Crop the picture so that only the fruit and basket display. (2) ●● Apply a soft edge oval frame to the picture. (2) 1.3. Insert the file Fruits.docx as a linked object below the image. The file must be displayed as an icon. (3) 2. The organisers of the festival needs to know how many learners will attend the festival. Make the following changes on page 2: 2.1. Open the spreadsheet 2Harvest and work in the Attendance worksheet. a. Copy and paste the information found in the worksheet as a linked table on page 2 of your word processing document. (3) b. Modify the tear-off slip part as shown below. Remember to update any changes. (13) ●●Change the row height of all the rows of the table to exactly 20. ●●Merge and centre row 11 of the table. ●● Remove the cell borders of rows 10 and 11 of the table as shown. ●● Insert a row at the bottom of the table. ●● Change the outside border of only the bottom three rows to a double-line border. ●● Make sure the font for the entire table is Cambria and 14pt. ●● Type your information in rows 4-8. 2.2 Insert a 3pt shadow-page border for the entire document. (3) Save and close both documents. TOTAL: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use a built-in template in a word processor? 2 Create your own template in a word processor? 3 Link objects in a word processor? 4 Import different file formats into a word processing document? 24 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 24 2019/09/20 11:53 AM

TERM 1 CHAPTER EDITING 3 CHAPTER OVERVIEW Unit 3.1: Editing By the end of this chapter, you will be able to: ●● Use Paste Special. ●● Use Find and Replace. INTRODUCTION In Grade 10, you learned about the basics of editing in a word processor, as well as using Find and Replace. In this section, you will learn about using Paste Special. You will also learn about the different techniques that you can use when replacing in a document. term 1 I chapter 3 Editing 25 Gr11_CAT_Practical_LB.indb 25 2019/09/20 11:53 AM

UNIT 3.1 Editing PASTE SPECIAL When you are pasting something you have copied, you can choose how you want to paste it. Paste Special in Microsoft Word allows you to do various things when pasting from other sources. When you have copied text or graphics from another file, select the drop-down arrow under Paste in the Home ribbon to see Paste Special. Figure 3.1: Paste special You will get different options depending on what it is you want to paste. For text, you will get the following options: Figure 3.2: Paste Special options for text For graphics, you will get the following options: Figure 3.3: Paste Special options for graphics 26 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 26 2019/09/20 11:53 AM

Activity 3.1 1. Copy and paste text. a. Open a blank word processing document. b. Open the Act3.1(Party) document, and then select the text from the words “Blocos are groups”, to the words “suitable for families”, then copy the selected text. c. Paste these words without formatting into the new document. d. Then paste the same words with their original formatting. e. Then paste the same words as a Microsoft object. f. Save (with an appropriate name) and close the document. 2. Copy and paste pictures. a. Open a blank word processing document. b. Open the Act3.1(Flora) document, and then select the picture of the flowers and copy it. c. Paste it as a JPEG in the new document. d. Open the Flora document again, and select the text from the words “Fynbos is the major vegetation” to the words “best to exclude them”, then copy them. e. Paste it as an image in the new document. f. Save and close the document. 3. Copy and paste mixed content. a. Open the Act3.1(Party) document, and then select all the content from the words “Individual Blocos” to the words “popular with young people”, copy the selected text. b. Paste it as formatted text. c. Insert a page break. Then Paste the same content as unformatted text. d. Save and close the document. FIND AND REPLACE As you learned in Grade 10, you can use the Find and Replace function in Word to find and replace words or phrases. You can do this by either selecting Replace in the Home ribbon and opening the menu or by pressing Ctrl+H to open the menu. There are other replace options you can use, like those shown in Figure 3.4. Once you have opened the Replace command, you need to select More>>. Figure 3.4: Find and Replace options term 1 I chapter 3 Editing I Unit 3.1 Editing 27 Gr11_CAT_Practical_LB.indb 27 2019/09/20 11:53 AM

If none of these replace options have been chosen, all instances of the word will automatically be replaced. ●● Find whole words only: For example, if you choose to replace the word “man” with “person”, things like “manual” will be changed into “personal’”. You will then need to go through the document and correct this, which can take a very long time. Tick Find whole words only to prevent this before you replace. ●● Match case: If you tick Match case, only words where the case in each character matches the search term will be replaced. For example, you would select this if you wanted to replace “CAT” the subject (and not “cat” the animal) with “Computer Applications Technology”. Only CAT in upper case will be replaced. ●● Format: You can also replace text that is formatted in a particular way using the Format option. For example, if you wanted to replace all the examples of the word “CPU” that are in bold with “central processing unit” in green text, you can use that option for formatting the text to green. ●● Special: Using the Special option lets you replace punctuation and special characters. For example, you can replace any white spaces with non-breaking spaces, or em dashes (—) with en dashes (–). In the Find What box, type everything you are looking for. Click in Replace with and type in the words/phrases which must replace what was typed in the previous box. Do any necessary formatting while in this box. Activity 3.2 1. Find and replace a word. a. Open a blank Word document. b. Open the Act3.2(Statistics) document and select and copy all the text. c. Paste the content with its original formatting. d. Find the word ‘healthy’, and delete it. e. Find the word ‘fast food’ and replace it with ‘fast food’. f. Find the word ‘fries’ and replace it with ‘Fries’. g. Replace the word ‘people’, with the word ‘humans’ bolded. h. Save and close the document. 2. Find and replace a sentence. a. Open the Act3.2 (Tips) document, and select the text from the words “Don’t believe what you read” to the words “your kid’s mud pies”, then copy them. b. Paste the content at the end of the document with its original formatting. c. Find the words ‘healthy food tips’ in the document and replace them with ‘dietary cuisine suggestions’. d. Remove any double spaces. Replace them with single spaces. e. Remove any sentences that start with the words ‘Just make’. f. Save and close the document. 28 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 28 2019/09/20 11:53 AM

REVISION ACTIVITY 1. Open the Chapter 3_Revision Activity word processing document and edit and format this document according to the instructions below: a. Adjust the logo to appear as follows: (3) b. Group the two parts that make up the logo and resize this logo to 5 cm x 5 cm to appear to the left of the words Travel Safely. (4) c. Set and apply appropriate tabs so that the text below the first paragraph appears as follows: (4) d. The word “insurance” has been spelt incorrectly as “insurrance”. Replace the incorrect spelling with the correct spelling and highlight the corrected text with the colour yellow. (3) e. Set the text on only the first page to be fully justified. (2) f. Set the paragraph spacing in the document to 3pts before and after each paragraph. (3) g. The word tour was mistakenly used instead of the word travel. Replace ALL occurrences of the word tour with the word travel in bold. (4) h. Place a red 2pt border around ALL the text under the first heading ARE YOU PLANNING AN OVERSEAS TRIP?. (3) i. Use the document: 3Highlights.docx. Copy the text in this document and paste special as a Microsoft Word Document object at the bottom of the document. (4) Save and close the document. TOTAL: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use Paste special in a word processor for different file types? 2 Use find and replace in a word processor? 3 Use the different options available in word processor to find and replace word/s in a document? term 1 I chapter 3 Editing I Unit 3.1 Editing 29 Gr11_CAT_Practical_LB.indb 29 2019/09/20 11:53 AM

TERM 2 CHAPTER PAGE AND DOCUMENT LAYOUT 4 CHAPTER OVERVIEW Unit 4.1: Page layout and design Unit 4.2: Document layout Unit 4.3: Paragraphs By the end of this chapter, you will be able to: ●● Add themes and cover pages to Word documents. ●● Add section breaks and headers and footers to Word documents. ●● Customise bullets and numbering in paragraphs. INTRODUCTION In this section you will learn to select themes and cover pages to enhance your word processing documents. You will use section breaks, headers and footers to give your documents a more professional look. and lastly, you will find out how to customise bullets and numbering in your documents. 30 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 30 2019/09/20 11:53 AM

UNIT 4.1 Page layout and design THEMES Microsoft Word comes with a variety of themes designed to allow you to customise documents instantly. Each theme uses a variety of colours, fonts and styles to make the document consistent without you needing to create a template for the document. Themes are found under the Design tab in Microsoft Word. You can customise themes by changing the colours, font, paragraph spacing and effects in the document. You can then choose to set your created theme as the default theme for Microsoft Word documents. Using themes allows you to format the whole document quickly, especially if you have used the Styles in the Quick Style gallery to apply headings and title styles to sections in your document. Themes do not overrule the style, but enhances the style (for example, making all the level 1 headings the same colour). Guided activity 4.1 Step 1: Select the Design tab. Step 2: Select a theme. Step 3: To change colours, select Colors and select the colour scheme you prefer. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.1 Page layout and design 31 Gr11_CAT_Practical_LB.indb 31 2019/09/20 11:53 AM

Guided activity 4.1 … continued Step 4: To change fonts, select Fonts. You can format the text elements in a theme exactly the way you would format text that has not been styled using a theme. Activity 4.1 1. Apply a theme. a. Open a blank word processing document. b. Import the text from Act4.1(Guide) with its original formatting. c. Apply the Crop theme to the document. d. Save the document as Act4.1(Guide_Theme) and close the document. 2. Change a theme. a. Open a blank Word document. b. Import the text from Act4.1(Guide) with its original formatting. c. Apply the Headlines theme (or any theme on the computer). d. Change the theme colour to Red Orange. e. Change the page colour to an appropriate background of your choice. f. Change the theme font to Century Gothic. g. Save the document as Act4.1(Guide_Theme2) and close the document. 3. Create your own theme. a. Import the text from Act4.1(Guide) in its original formatting. b. Apply any theme of your choice. c. Create and apply your own customised theme colour. Name it `MyStyle’. d. Create and apply your own theme font. Name it `MyFont’. e. Add a page border. f. Save the document as MyTheme.docx and close the document. 32 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 32 2019/09/20 11:53 AM

COVER PAGE You can also add attractive cover pages to documents using built in tools. To insert a cover page, follow the steps in Guided activity 4.2 below. Guided activity 4.2 Step 1: Select the Insert tab. Step 2: Select Cover Page. Step 3: Choose the cover page you want. Step 4: Complete the text fields with your own text on the cover page. term 2 I Chapter 4 Page and Document Layout I Unit 4.1 Page layout and design 33 Gr11_CAT_Practical_LB.indb 33 2019/09/20 11:53 AM

Using a cover page is very important for large documents and reports. In your PAT, you will need to use a cover page that will have all your personal details on it, making it easier for your assessor to see who the document belongs to. Activity 4.2 Complete the following activity on your own. Note: Go to the options setting and change the ‘User name’ field to your name and surname. 1. Apply a cover page. ●● Open a blank document. ●● Apply a cover page of your choice. ●● Assume that this cover page is for an essay about global warming. Fill in the empty fields (placeholders) on the page with appropriate content. ●● Save the document as GlobalWarming and close the document. 2. Create a cover page. ●● Open a blank document. ●● Apply a cover page of your choice. ●● Change the position of at least ONE element on the page. ●● Change the colour of at least ONE element on the page. ●● Change the font size of at least ONE element on the page. ●● Insert and position a text box of your choice. ●● In the text box write a short paragraph explaining what elements you have changed on the cover page. ●● Save the document as CoverPage and close the document. 34 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 34 2019/09/20 11:53 AM

UNIT 4.2 Document layout Section breaks are used to divide documents into sections to format individual pages. Each page can have different layouts including columns, headers, orientation and spacing. In this unit, you will learn about the different types of section breaks. SECTION BREAKS AND SECTIONS There are different types of section breaks: ●● A Next Page section break starts the new section on the following page. ●● A Continuous section break starts the new section on the same page. One of the most common reasons for using this type of section break is if you’re using columns. By using this sort of section break you can change the number of columns without starting a new page. ●● An Even Page section break lets you start a new section on the next even- numbered page. ●● An Odd Page section break lets you start a new section on the next odd- numbered page. ●● Apart from Section Breaks you will also notice Page Breaks, these will not create a new section, but will move text below the break to the top of a new page: ●● A Column Break is used when a page is divided into columns and you want to move text to the top of a column. When working with breaks remember to use Show/Hide to see where the breaks are placed. term 2 I Chapter 4 Page and Document Layout I Unit 4.2 Document layout 35 Gr11_CAT_Practical_LB.indb 35 2019/09/20 11:53 AM

Guided activity 4.3 Step 1: Place your cursor where you want the section break to start. Step 2: Select the Layout tab. Step 3: Select the drop-down arrow next to Breaks. Step 4: Select the type of break you want to use. It will be inserted where you placed your cursor. The section break we chose here was Next Page. Activity 4.3 Complete the following activity on your own. Use section breaks. ●● Open a blank Word document. ●● Import the content from Act4.3(1Artists) in its original formatting. ●● Insert a continuous section break after the words “in South Africa (insert citation here).”. ●● Insert a next page section break after the words “engages with issues in modern culture.”. ●● Insert an even page section break after the words “multiple exhibitions in South Africa and abroad.”. ●● Insert an odd page section break after the words “internationally as well as in his home country.”. ●● Insert a column break after the words “attraction and repulsion that cowhide evokes in the viewer.”. ●● Save the document as Artist(Your Name) and close the document. 36 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 36 2019/09/20 11:53 AM

HEADERS AND FOOTERS Headers and footers are usually used in multi-page documents to give descriptive information about the document, such as the title, revision date, author’s name or even images. This is also where page numbers are inserted. Using headers and footers together with sections allow you to add different numbering styles to different sections. For example, the first few pages of a document contain Roman numbers (i, ii, iii, etc.) and the rest of your document Arabic numerals (1, 2, 3, 4, etc.). Guided activity 4.4 Step 1: Select Insert. Step 2: Select Header or Footer in the Header & Footer group. Headers and footers use different information depending on what type of document you are creating. This could be a date, the name of the document or even the author’s name. Guided activity 4.5 To add a date and time (that can be automatically updated): ●● Double click on the Header or Footer. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.2 Document layout 37 Gr11_CAT_Practical_LB.indb 37 2019/09/20 11:53 AM

Guided activity 4.5 … continued ●● Select Date & Time in the Insert group. ●● Select the date format you want to use. ●● Tick the box for Update automatically. ●● Select OK. To insert the file path, file name or author name: ●● Double click on the Header or Footer. … continued 38 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 38 2019/09/20 11:53 AM

Guided activity 4.5 … continued ●● Select Document Info in the Insert group. ●● Select the appropriate item, in this case, it is the File Name. ●● This should automatically insert the information you selected. When you use different sections, you can choose to link or unlink your headers and footers. The headers and footers in a Word document are automatically linked to each other across sections. To unlink (or re-link) headers and footers, follow the steps in the example below. Guided activity 4.6 Step 1: Double-click in the header or footer of the section you want to unlink. Step 2: Select the Design tab under Header & Footer tools. Take note Your screen may look slightly different; it depends on the program you’re working in … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.2 Document layout 39 Gr11_CAT_Practical_LB.indb 39 2019/09/20 11:53 AM

Guided activity 4.6 … continued Step 3: Select Link to Previous to unlink your header or footer from the previous section. If your sections are unlinked, you can select Link to Previous to link them again as well. Step 4: Select the arrow next to Page Number in the Header & Footer group. Step 5: Select Format Page Numbers and then select Start at. Enter the number you want to start with and then select OK. 40 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 40 2019/09/20 11:53 AM

Page numbers help you track where you are in a document and are useful when the document has been printed and you need to sort it. You can have different types of page numbers as well – you can choose to have the first page be different from the rest (for example, not have a number), you can choose to have different page number layouts for odd and even pages, and you can even start numbering your document at a specific number. Guided activity 4.7 Step 1: Select Insert. Step 2: Select Page Number. Step 3: Choose where you want the page numbers to be. Step 4: Choose the type of page numbering you want. Step 4a: To have a different first page, make sure the Different First Page box is ticked. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.2 Document layout 41 Gr11_CAT_Practical_LB.indb 41 2019/09/20 11:53 AM

Guided activity 4.7 … continued Step 4b: To have numbering for odd and even pages, tick Different Odd & Even Pages. If you have selected this option, remember that you will have to insert the page number in both an Odd page and an Even Page to be displayed. Step 4c: Go to the Header or Footer tools and select the style that you want to use. You can also do this manually. Step 4d: Use the Format Page Numbers menu (in the Page Numbers drop-down) to either have numbers carry on when there is a section break or to start numbers from a specific number. Activity 4.4 Complete the following activity on your own. 1. Apply headers and footers. ●● Open a blank Word document. ●● Import the text from Act4.4(Global). ●● Insert the Facet (Even page) header. ●● Change the header’s font colour to yellow, bold, 14 pt. ●● Insert the Facet (Odd page) footer. ●● Change the footer’s title to `African Art’. ●● Change the footer’s subtitle to `Copyright@2019’. ●● Save the document as GlobalHeader(YourName) and close the document … continued 42 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 42 2019/09/20 11:53 AM

Activity 4.4 … continued 2. Modify the header and footer elements. ●● Open a blank Word document. Take note ●● Import the content from the document Act4.4(Top15SA), in its original formatting. ●● Change the footer as follows: Go to the options setting ¡¡ Align the page number in the centre. and change the ‘Username’ ¡¡ Add the author name and align it to the right-hand side. field to your name ●● Change the header as follows: and surname. ¡¡ Add the date (YYYY-MM-DD) that automatically updates on the right-hand side. ¡¡ Add the file name and align it to the left-hand side. ●● Save the document as Top15SAHeader(YourName) and close the document 3. Unlink and link footer sections. ●● Open a blank Word document. HINT ●● Import the content from Artists in its original formatting. Use the Plain ●● Add a next page section page after the words “multitude of cultures surrounding them.”. Number options in all cases. ●● On the first page, Use Page Number to align the page number to the bottom left. ●● Change the numbering format for the section before the section break to A, B, C, etc. ●● Restart the numbering for the section after the section break. ●● Save the document as ArtistsFoooter(YourName) and close the document. 4. Unlink and link header and footer sections. ●● Open a blank Word document. ●● Import the content from Artists in its original formatting. ●● Add a page break after the words “that engages with issues in modern culture.”. ●● Add a next page section page after the words “multiple exhibitions in South Africa and abroad.”. ●● Add an odd page section break after the image below “Figure 3: Freedom Fighters 2024”. ●● Add a next page section page after the words “internationally as well as in his home country.”. ●● On the first two pages: ¡¡ Use Page Number to add the page numbering on the top of the page (centre aligned). ¡¡ Remove the numbering on the footer of the first 2 pages. ●● On page 3: ¡¡ Use Page Number to add the page numbering on the top of the page (left aligned). ¡¡ Remove the numbering on the footer. Take note ●● On the last two pages: Make sure that the ¡¡ Use Page Number to add the page numbering on the top of the page (centre aligned). appearance of the odd and Hint: Use the Plain Number options. even pages match. ●● Save the document as ArtistsPageNumber(YourName) and close the document. term 2 I Chapter 4 Page and Document Layout I Unit 4.2 Document layout 43 Gr11_CAT_Practical_LB.indb 43 2019/09/20 11:53 AM

UNIT 4.3 Paragraphs In Grade 10, you learned how to apply basic formatting to a paragraph and how to add bullets or numbering to your text. You can customise these bullets and numbers by using the tools in the Paragraph group in the Home ribbon. Guided activity 4.8 Step 1: Select the text section you want to have in a list. Step 2: Select Bullets or Numbering in the Paragraph group. Step 3: To change the bullet or number style, select the drop-down arrow and select the bullet/ number you want to use. Step 4: To change the level of the bullet/number, place your cursor on the line you want to change. Step 5: Select Increase Indent or use the Tab key on the keyboard. 44 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 44 2019/09/20 11:53 AM

MULTI-LEVEL LISTS Sometimes you will need lists with multiple levels. If your levels are not too complex (for example, you need a sub-bullet under a main bullet), you can very often just place your mouse cursor at the start of the sentence you want to make a second level and press Tab on your keyboard. This will automatically move the bullet or number further from the margin and add a different number or bullet style at the start. However, when your list becomes complex (such as numbered headings or multiple lists in lists), you will need to set up a multi-level list. Guided activity 4.9 Step 1: Select the text or numbered list you want to change. Step 2: Select the arrow next to Multilevel List in the Paragraph group on the Home tab. Step 3: Select Define New Multilevel List. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 45 Gr11_CAT_Practical_LB.indb 45 2019/09/20 11:53 AM

Guided activity 4.9 … continued Step 4: Select More in the bottom left corner to expand the Multilevel list dialogue box. Step 5: Choose the list level to modify by selecting it in the list. 1 is selected by default. Step 6: Choose where you would like to apply your changes by selecting Whole list, This point forward or Current paragraph. … continued 46 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 46 2019/09/20 11:53 AM

Guided activity 4.9 … continued Step 7: Choose the number to show in the gallery. Level 1 is shown by default. Step 8: Give the multilevel list a name in the ListNum field list name. This name will appear wherever you see the Listnum field. Step 9: To add a dash, brackets or other value to the end of the numbered list, enter it in the Number format field. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 47 Gr11_CAT_Practical_LB.indb 47 2019/09/20 11:53 AM

Guided activity 4.9 … continued Step 10: To change the font size, style and colour, select Font and choose a setting on the Font tab or Advanced tab. Step 11: To change the style, select the down arrow next to Number style and choose numbers, letters or another format. Specify a level number to include this from. … continued 48 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 48 2019/09/20 11:53 AM

Guided activity 4.9 … continued Step 12: Choose the number the list should start at. The default value is 1. To restart numbering after a specific level, select the Restart List After check box and choose a level in the list. Step 13: Select Legal Style Numbering to enforce a legal style on the multi-level list. Step 14: To change the number alignment, choose Left, Centered or Right under Alignment. Specify a value for where to start the alignment and a value for the text indent. To apply these values to the entire list, choose Set for All Levels. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 49 Gr11_CAT_Practical_LB.indb 49 2019/09/20 11:53 AM

Guided activity 4.9 … continued Step 15: Enter a value for what should follow each number, Tab character, Space, or Nothing. Check Add tab stop at and enter a value. Step 16: Select OK. LINE SPACING You may also need to customise your line spacing in the document, for example, adding a double-line space or a one-and-a-half-line space between each line of text. Guided activity 4.10 Step 1: Select the text of which you want to change the line spacing. … continued 50 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 50 2019/09/20 11:53 AM

Guided activity 4.10 … continued If you need to change the line spacing in the whole document, you can select all the text (use Ctrl+A on the keyboard) or change the line spacing in the Normal style. Step 2: Select the Line and Spacing icon in the Paragraph group on the Home tab. Step 3: Choose a spacing option from the list. Step 4: If the list does not have the option you need, select Line Spacing Options. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 51 Gr11_CAT_Practical_LB.indb 51 2019/09/20 11:53 AM

Guided activity 4.10 … continued Step 5: In the Paragraph dialogue box, select the drop-down list under Line Spacing and then choose the option you want. Step 6: Select OK. COLUMNS Columns are a way to format text to change the layout of a page filled with text. To insert columns, follow the steps in the example below. This feature can be set up before your document has text, or if the text is already typed, you need to select the text which should be placed in columns. Guided activity 4.11 Step 1: Select the Layout tab. Step 2: Select Columns in the Page Setup group. … continued 52 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 52 2019/09/20 11:53 AM

Guided activity 4.11 … continued Step 3: Select the number of columns you want. Step 4: To add a break between columns, place your cursor where you want to add the break and then select the arrow next to Breaks. Step 5: Select Column. This will move text to start at the top of a new column. Step 6: To adjust the spacing between columns, select Columns and then select More Columns. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 53 Gr11_CAT_Practical_LB.indb 53 2019/09/20 11:53 AM

Guided activity 4.11 … continued Step 7: Use the up and down arrows to adjust the space between the columns and the width of the columns. To have columns of equal width, tick the Equal column width box. Step 8: Check the box for Line Between if you need a vertical line to run between columns. Step 9: Select OK. Activity 4.5 Complete the following activity on your own. 1. Apply numbering to text. ●● Open a blank Word document. ●● Open the document Act4.5(Pollute), copy the text from “Pollution is the addition” to the first occurrence of “Soil Pollution” and paste it unformatted. ●● Select the text from the words “Air and Sound Pollution” to the words “Soil Pollution”. ●● Apply the following numbering: Bold numbered list 1), 2), 3), with a left and right indentation of 0 cm, that hangs by 0,63 cm. ●● Save the document as PolluteNumbering and close the document. 2. Apply bulleting to text. ●● Open a blank Word document. ●● Open the document Act4.4(Pollute), copy the text from “Water pollution is the introduction” to the first occurrence of “must be effectively controlled” and paste it unformatted. ●● Select the text from the words “Examples of Water Pollution” to the words “must be effectively controlled”. ●● Apply the black square bulleting. Make sure the bulleting has a left and right indentation of 0 cm, that it hangs by 0,63 cm. ●● Then indent all the text under the words “Examples of Water Pollution” and above the words “Control Measures”. ●● Also indent all the text below the words “Control Measures”. ●● Further indent the paragraphs below the following words: ¡¡ “Industrial Effluent” ¡¡ “Mining and Agricultural Wastes” … continued 54 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 54 2019/09/20 11:53 AM

Activity 4.5 … continued ¡¡“Sewage Disposal and Domestic Wastes” ¡¡“The following measures can be used to stop water pollution:” ●● The first bulleting level must have black square bullets. ●● The second bulleting level must have black circle bullets. ●● The third bulleting level must have arrows. ●● Save the document as PolluteBulleting and close the document. 3. Change and apply a new multi-list levelling. ●● Open a blank Word document. ●● Open Act4.5(Fynbos) and copy the text from “Twenty-six species of fynbos plants” to “Care for the Earth Day” unformatted. ●● Select the text from “The promotion of public” to “Care for the Earth Day” and do the following: ¡¡ Apply a first level numbering style to the first three sentences (1 – 3). ¡¡ Apply a second level numbering style to the next three sentences (4 – 6). ¡¡ Apply a first level numbering style to the next two sentences (7 – 8). ¡¡ Apply a second level numbering style to the next sentence (9). ¡¡ Apply a third level numbering style to the next two sentences (10 – 11). ¡¡ Apply a fourth level numbering style to the remaining sentences (12 – 13). ●● The first level must be as follows: ¡¡ Numbering style: 1, 2, 3; Times New Roman; Left; text indent: 0,63 cm; aligned at: 0 cm. ●● The second level must be as follows: ¡¡ Numbering style: 1.1, 1.2, 1.3; Font: Times New Roman; Alignment: Left; Text indent: 0,63 cm; Aligned at: 0,63 cm. ●● The third level must be as follows: ¡¡ Numbering style: a, b, c; Font: Times New Roman; Alignment: Left; Text indent: 0,63 cm; Aligned at: 1,1 cm; Font colour: Purple; Font style: Italic. ●● The fourth level must be as follows: ¡¡ Numbering style: i, ii, iii; Font: Calibri; Alignment: Left; Text indent: 1 cm; Aligned at: 1,5 cm; Font style: All caps; Font size: 8 pt. ●● Save your document as FynbosNumbering and close the document. 4. Change line spacing. ●● Open a blank Word document. ●● Import the content from the document Act4.5(Ruga) unformatted. ●● Select all the text and change the line spacing as follows: Before: 0 pt; After: 12 pt; At least: 20 pt line spacing. ●● Change the spacing for the Normal style as follows: Before: 0 pt; After: 8 pt; Single line spacing. ●● Re-apply the Normal style to the text from “Bursting with eclectic multicultural” to “Venice Biennale in 2013.” ●● Save the document as RugaLineSpacing and close the document. 5. Change the line spacing of a new style. ●● Open a blank Word document. ●● Import the content from the document Act4.5(Ruga) with its original format. ●● Change the Thick Normal style’s as follows: Before: Auto; After: 18 pt; Multiple line spacing of 1,5 cm. ●● Select the text from “Athi-Patra Ruga was” to “becoming a hybrid construct.”. ●● Format the text as follows: Font colour: Red; Font size: 11 pt; Font style: Bold italic; Before: 2 pt; After: 2 pt; 1,5 lines spacing. … continued term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 55 Gr11_CAT_Practical_LB.indb 55 2019/09/20 11:53 AM

Activity 4.5 … continued ●● Import the content from Global in its original formatting. ●● Add a column break after the words “social, political and cultural positions”. ●● Add a next page section break before the words “Insert linked object’’. ●● Save this new style as ‘Small Red Thick’. ●● Save the document as RugaLSpacing and close the document. 6. Insert columns. ●● Open a blank Word document. ●● Create two columns. ●● Import the content from Act4.5(Global) in its original formatting. ●● Add a column break after the words “social, political and cultural positions”. ●● Add a next page section break before the words “Insert linked object’’. ●● Select the text from the words “Travel tips & planning info” onwards and apply a three- column layout. ●● Save the document as GlobalColumns and close the document. 7. Modifying columns. ●● Open a blank Word document. ●● Import the content from Act4.5(Global). ●● Insert a page break after the words “Willie Bester, Athi-Patra Ruga and David Koloane.” ●● Insert a continuous section break after the words “to examine the process of art-making itself.”. ●● Select the text from the words “There are also a number of exiting” to “to examine the process of art-making itself.” and do the following: ¡¡ Apply the right column layout. ¡¡ Change the line spacing of the text to 2.0 cm. ¡¡ Change the column spacing as follows: Width of column 1: 9 cm; Spacing: 2 cm; add a line between the columns. ●● Save the document as GlobalModColumns and close the document. REVISION ACTIVITY 1. Open the Chapter 4_Revision Activity 1 word processing document and do the following: a. Apply any theme to the document and add a cover page with the text “Happy St Patrick’s Day” as the title. (4) b. Add your name in the author control. Add the date field in the format 01-Jan-17 anywhere on the page. (4) c. Add a header to the document. Insert the “Blank” header style. Change the header’s font colour to red, bold and 14 pt. The header must contain the Filename. (6) d. Insert automatic page numbering at the bottom of the page as follows: (6) i. The first page must NOT be numbered. ii. Odd pages must be right aligned and even pages left aligned to the footer. e. In the sections “Common traditions include” and “Worldwide St. Patrick’s Day Parades and Celebrations” make the text a bullet list. (2) f. Under “Common traditions include” add a second level to the first bullet listing the cities that hold large parades. (2) g. Add a section break between “The History of St. Patrick’s Day and why it’s celebrated” and “Worldwide St. Patrick’s Day Parades and Celebrations”. (1) h. Take the text under “The History of St. Patrick’s Day and why it’s celebrated” and convert it to two columns with a line between. Set the width of the columns to 6.5 cm. (4) Save and close the document. … continued 56 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 56 2019/09/20 11:53 AM

REVISION ACTIVITY … continued 2. Open the Chapter 4_Revision Activity 2 word processing document. 2.1. Format the first two paragraphs in the document as follows: a. Change the line spacing of the first paragraph in the document to Double. (1) b. Indent the FIRST line of the second paragraph to 2 cm. (1) 2.2. Change all the paragraphs of the document to a bulleted list. Apply bullets as follows: a. Insert the 4Gold image as a bullet. (2) b. Align the bullet 0.5 cm from the margin. (1) c. Set the text indent at 2 cm. (1) TOTAL: [35] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use the different themes in a word processor? 2 Insert a cover page in a word processor? 3 Insert section breaks and sections? 4 Insert multi-level lists? 5 Use line spacing in paragraphs? term 2 I Chapter 4 Page and Document Layout I Unit 4.3 Paragraphs 57 Gr11_CAT_Practical_LB.indb 57 2019/09/20 11:53 AM

TERM 2 CHAPTER ELECTRONIC FORMS AND 5 ONLINE/OFFLINE HELP CHAPTER OVERVIEW Unit 5.1: Electronic forms Unit 5.2: Online/offline help By the end of this chapter, you will be able to: ●● Create and manage fillable electronic forms. ●● Troubleshoot common Microsoft Word problems and access the Help functions. INTRODUCTION In this section you will learn to create forms in word prcessing documents that allows users to fill in certain fields. You will also find out how to solve common word prcessing problems. Questionnaires are powerful instruments to gather information from people. Questionnaires are emailed to respondents to fill in electronically. MS Word allows you to include text boxes, dropdown lists, checkboxes and other form field controls in your document. The forms can also be printed if necessary. 58 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 58 2019/09/20 11:53 AM

UNIT 5.1 Electronic forms You can create electronic forms in Microsoft Word for a variety of uses. These forms are basically a word processing document with special properties that allow users to fill in certain fields with information or to answer questions. You can prevent users from making changes to the document and only using the fields you create by protecting the document. To create a form, you first need to make sure that you can access the tools necessary to create the fields in the form. You will need to enable the Developer Tab on the Ribbon: ●● Select File. ●● Select Options. ●● Select Customize Ribbon. Figure 5.1: Customize Ribbon option ●● Tick the box next to Developer. Guided activity 5.1 Step 1: Write the content you need. Step 2: Select the Developer tab. Step 3: Select Legacy tools in the Controls group. … continued term 2 I Chapter 5 Electronic forms and Online/Offline Help I Unit 5.1 Electronic forms 59 Gr11_CAT_Practical_LB.indb 59 2019/09/20 11:53 AM

Guided activity 5.1 … continued Step 4: Place your cursor in the document where you want to insert a form field. Step 5: Select the Legacy Tools icon to access the control fields you will be using. ICON NAME DESCRIPTION Form field types Text Form Field Used for text. Only the field can be formatted, not the text inside the field. Check Box Form Field Creates an on/off check box. Drop-Down Form Field Displays a list you specify. Users cannot add their own entries. Form field tools Insert Frame Creates a frame. Frames hold static content. Form Field Shading Switches form field shading on or off. Reset Form Fields Clears all the entries in fields Step 6: Select Properties to add content to your fields. In this example, we are adding numbers to a drop-down list. Here we have added 1 and typed 2 into the Drop-down item box. Select Add to add it to the list and then select OK. … continued 60 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 60 2019/09/20 11:53 AM

Guided activity 5.1 … continued Step 7: Use the Restrict Editing tool in the Protect group to limit what people can change in the document. You can stop people from changing styles or make changes at all, other than just filling in the form fields. Once you have set what other users can do in the document, select Yes, Start Enforcing Protection, give the document a password and then select OK. Take note You can also customise form fields by using the form fields’ properties and by configuring Giving the document a password to be opened them correctly. This means that you can use a text field to insert dates, numbers or text with with, is optional. special formatting. Legacy form fields have a variety of configuration options available. Guided activity 5.2 Step 1: Insert a Text form field. Step 2: Right-click on the form field or select Properties in the Developer tab to open the Properties dialogue box. Step 3: Choose your field type: Step 4: Enter your properties in the fields and then select OK. term 2 I Chapter 5 Electronic forms and Online/Offline Help I Unit 5.1 Electronic forms 61 Gr11_CAT_Practical_LB.indb 61 2019/09/20 11:53 AM

If you choose Regular text, the following options are available: ●● Default text, which lets you specify the text that appears in the field before the user enters their own text. ●● Maximum length allows you to restrict the number of letters in the field. ●● Text format lets you specify if the text in the field to be uppercase or lowercase only or if the text should start with a capital letter or be in title case. If you choose Number, you will have the following options: ●● Default number, which lets you specify the number that appears in the field before the user enters their own. ●● Maximum length is measured in the number of digits you specify. ●● Number format is a drop-down list that lets you choose a specific number format, like currency or percent. If you choose Date, you get the following options: ●● Default date works just like Default text. ●● Maximum length lets you specify the number of digits in a date. You don’t really need to specify this, as dates can only be a certain length anyway. ●● The Date format drop-down list lets you specify the date format (for example dd-MM- yyyy or yyyy-MM-dd). ¡¡ dd for the day i.e. dd for 05, ddd for Mon, dddd for Monday ¡¡ MM for the month i.e. MM for 12, MMM for Dec, MMMM for December ¡¡ yyyy for the year i.e. yy for 19, yyyy for 2019 62 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 62 2019/09/20 11:53 AM

UNIT 5.2 Online/Offline help When you run into problems using Microsoft Word, you can turn to the built-in Help function or look online for help for your issues. Note that Microsoft’s help function is not available offline in newer versions from 2016, onward. Guided activity 5.3 To access the built-in Help function, you can: Step 1: Enter a query in the box labelled Tell me what you want to do at the end of the tabs at the top of the document. Step 2: Press F1 on your keyboard to enter the Help panel on the right-hand side of the document. MICROSOFT SUPPORT Step 3: Select File and then select the “?” in the top right-hand corner. You can also go online and search for your problem. Usually, your top results will be the Microsoft support website, where you will be able to find tutorials for Microsoft products as https://support.microsoft. com/en-za well as answers to frequently asked questions. term 2 I Chapter 5 Electronic forms and Online/Offline Help I Unit 5.2 Online/Offline help 63 Gr11_CAT_Practical_LB.indb 63 2019/09/20 11:53 AM

Activity 5.1 1. Create a form. a. Open a blank document. b. Add the following fields: ●● Add a first name field. Add form field shading. ●● Add a surname field. Limit this field to 20 characters and add form field shading. ●● Add a date field. Use the YYYY/MM/DD format and add form field shading. c. Create FIVE yes or no questions and add a check box at the end of these questions. d. Create THREE questions with a limited amount of possible answers. Hint: Use the drop-down list. e. Add a simple signature line at the bottom. f. When you are done, add a password of 123 to restrict editing. g. Save the document as Act5.1_Q1(YourName) and close the document. 2. Edit a form. a. Open a blank Word document. b. Open the Act5.1(School) document and copy and paste the ‘Electricity Usage: Survey Form’ on the last page, with its original formatting. c. Change the Name field to a drop-down list field. Create THREE possible name options, but one of them should be your real name. d. Change the Surname field to a drop-down list field. Create THREE possible surname options, but one of them should be your real surname. e. Change the Age field to a Number text field. f. Restrict the Age field to THREE characters. g. Add a date field that does not update automatically. h. Add the following extra fields: i. Add an ID number text field. Restrict the field to 10 characters. ii. Add a Gender check box field. i. After the “How would you describe your usage of electricity?” question, insert a Frame around the blue highlighted text, expand the frame and position it under the question. j. When you are done, add a password of 123 to restrict editing. k. Save Act5.1_Q2(YourName) and close the document. Complete the following in your notebook: 3. When you use the online help function, what are the first TWO results that appear for: (Note: You will need an internet connection to use this.) A Background colour B Forms C Break D Data tool 64 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 64 2019/09/20 11:53 AM

REVISION ACTIVITY 1. A draft version of the electronic entry form for learners who would like to attend workshops has been created. Open the Chapter 5_Revision Activity 1 word processing document. The form uses legacy form controls. Some form field controls have been added to the form already. Change the form as follows: a. Set and apply appropriate tabs to align the form controls and the labels under the headings “Personal Information” and “Contact Information” as follows: ●● The first tab is set at 1 cm. (2) ●● The second tab is set at 11 cm. (2) b. Add the items “Male” and “Female” to the drop-down form field next to the word “GENDER”. (2) c. Add an appropriate form field control next to the text “ID NUMBER”. This field should only accept 13 characters. (2) d. Format the text form field control next to the text “DATE OF BIRTH” to allow only dates with the format yyyy/MM/dd. (2) e. Add a control next to the text “WEDNESDAY” that will be similar to the one above it in the form. (1) NOTE: Do NOT lock or protect the form 2. Open the Chapter 5_Revision Activity 2 word processing document. a. Format the electronic form at the end of the document to appear as follows: NOTE: Make use of the Legacy Form fields. … continued term 2 I Chapter 5 Electronic forms and Online/Offline Help I Unit 5.2 Online/Offline help 65 Gr11_CAT_Practical_LB.indb 65 2019/09/20 11:53 AM

REVISION ACTIVITY … continued b Switch on the Form Field Shading function of the application. (2) c. Add a Drop-down Form Field to the right of the word “Title” with the following options Mr, Mrs, Ms and Dr. (2) d. Add Check Box Form Fields to the left of the words “CAT” and “Tourism”. (2) e. Format the Text Form Field next to the heading “Today’s date” as follows: ●● change the Type to Current date, and the ●● format to yyyy-MM-dd. (2) f. Change the property of the Text Form Field for “Child’s Name” so that the name will automatically appear in uppercase when the user enters a name in the field. (1) g. Insert a table for the ID number next to the heading “ID Number”. The table must have 13 columns and one row, and the columns must be 0.5 cm wide. (3) h. Add a leader tab next to the word “Signature”. The leader tab must start at 6 cm and end at 14 cm. Use a solid line for the leader tab. (2) i. Place a 3pt wide double-line page border around the page. (3) j. Add your name to the author property of the document. (2) Save and close the document TOTAL: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Create a form in a word processor? 2 Add a drop-down form field? 3 Change the default value of checkboxes? 4 Use online/offline help in a word processor? 66 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 66 2019/09/20 11:53 AM

TERMS 1, 2, 3 CHAPTER STYLES 6 CHAPTER OVERVIEW Unit 6.1: Editing or creating styles By the end of this chapter, you will be able to: ●● Describe the difference between templates and styles. ●● Edit or change a style. ●● Create a new style. ●● Apply heading and paragraph styles. INTRODUCTION Styles are an easy way to quickly and consistently add formatting to Word documents. In this chapter you will learn about styles and how to create your own styles. You will also learn how to edit existing styles. termS 1, 2, 3 I chapter 6 Styles 67 Gr11_CAT_Practical_LB.indb 67 2019/09/20 11:53 AM

UNIT 6.1 Editing or creating styles In Grade 10, you learned about the Quick Style gallery. The Quick Style gallery is the easiest way to apply a style. You can find it on the Home tab in the Styles group. You can apply any style in the Quick Style gallery by selecting one of the styles. You can also see more styles by selecting the down arrow on the bottom-right corner. You can hover the mouse pointer over a style to see what it will look like in the document without having to select it. You can also edit specific styles to suit your document. This can include changing the spacing, numbering and font colour and size depending on what you want to do. Guided activity 6.1 Step 1: Open a blank word processing document. Select the arrow in the bottom right corner of the Styles group on the Home tab. This opens the Styles pane. Step 2: Select the down arrow next to the name of the style you want to change. Step 3: Select Modify. Activity 6.1 1. Open Act6.1(Pollute).docx. a. Edit the style “Heading 1” as follows: i. Format the font to all caps. ii. Make the text bold. iii. Change the font to Arial, 16pt. … continued 68 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 68 2019/09/20 11:53 AM

Activity 6.1 … continued b. Edit the style “Heading 2” as follows: i. Change the font to Arial, 14pt. ii. Make the text bold and underlined. iii. Format the text to be green. c. Edit the style “Heading 3” as follows: i. Format the text to be blue. ii. Make the font Arial, 11pt and bold. d. Edit the style “Normal” as follows: i. Make the text Arial, 11pt. You can change font type, size and colour of the style as well as changing the alignment, line and before/after spacing and the indentation. Selecting Format in the bottom left-hand corner also gives you the option to change the paragraph layout, add or remove tabs, borders, frames, numbering and text effects. You can also change the default proofing language or create a shortcut key. While the Quick Styles gallery and the styles are wonderful tools, you may want to create your own styles for your document, depending on your needs. Guided activity 6.2 Step 1: Select the arrow in the bottom right corner of the Styles group on the Home tab. Step 2: Select New Style in the bottom left corner of the style pane. Step 3: Give the style a name, choose its type and the style it is based on. … continued termS 1, 2, 3 I chapter 6 Styles I Unit 6.1 Editing or creating styles 69 Gr11_CAT_Practical_LB.indb 69 2019/09/20 11:54 AM

Guided activity 6.2 … continued Step 4: Choose the font type, size and colour for the new style. Step 5: Choose an alignment, line spacing and indents. APPLYING HEADING AND PARAGRAPH STYLES Once you have created the styles, you can apply them to headings and paragraphs. Heading styles are particularly important, since the headings need to be formatted to a specific Heading style for you to make a table of contents or an index. Guided activity 6.3 Step 1: Select the text you want to apply a style to. Step 2: In the Styles group, select the heading or paragraph style in the Styles gallery you want to apply. Step 3: Check that the style has been applied correctly. 70 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 70 2019/09/20 11:54 AM

Activity 6.2 1. Open Act6.2(Pollution).docx. Create a new style as follows: a. Base the style on “Heading 1”. b. Centre the text horizontally. c. Apply any type of paragraph border. d. Apply the new style to the heading in the document. REVISION ACTIVITY 1. Open the Chapter 6_Revision Activity word processing document and do the following: 1.1 Type your name and surname in the document subtitle of the cover page. Change the font colour to dark blue. (3) 1.2 Apply a double line 2pt page border that does not include the first page. (3) 1.3 Create new styles for the following headings. Use different font styles/sizes, colours, effects and spacing. a. Create a style for a Heading 1 and apply it to all text marked with h1. (4) b. Create a style for Heading 2 and apply it to all text marked h2. The style must be visually different from the Heading 1 style. (4) c. Create a style for Heading 3 and apply it to all text marked h3. The style must be visually different from both the Heading 1 and Heading 2 styles. (4) d. Modify the Normal style as follows: i. Change the font to Arial and the font size to 12pt. (2) ii. Change the spacing before and after to 3pt. (2) iii. Make the line spacing Multiple at 1.15. (2) 1.4 Change the height of the picture on page 2 to exactly 5cm and apply any art border of your choice. (3) 1.5 Use a formula to calculate the total of the Precious Metals Production 2015 in the space provided on the table on page 2. (3) Total: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Describe the difference between templates and styles? 2 Edit or change a style? 3 Create a new style? 4 Apply heading and paragraph styles? termS 1, 2, 3 I chapter 6 Styles I Unit 6.1 Editing or creating styles 71 Gr11_CAT_Practical_LB.indb 71 2019/09/20 11:54 AM

TERM 3 CHAPTER MAILINGS AND REFERENCES 7 CHAPTER OVERVIEW Unit 7.1: Mailings Unit 7.2: Reference By the end of this chapter, you will be able to: ●● Use Mail Merge for letters and labels. ●● Use the referencing features to add captions, footnotes, citations, bibliographies, indexes and tables of contents. INTRODUCTION In this chapter you will learn about how to do a mail merge to create personalised letters and labels. Lastly, you will learn about the referencing tools in Word and how to use them. These tools include: ●● Captions ●● Footnotes ●● Citations and bibliographies ●● Tables of contents 72 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 72 2019/09/20 11:54 AM

UNIT 7.1 Mailings The Mail Merge feature in Word helps you create personalised written communication more efficiently. You can create batches of personalised letters and emails that can include standard and customised content. All the data you use to customise communications is taken from a data source (a mailing list saved in Excel, Word or Access for example). You can also create and print personalised envelopes and mailing labels using Mail Merge. The Mail Merge process uses three documents: ●● The main document that contains images, text, etc. that are the same for each letter, email, envelope or label. ●● The mailing list, which contains the data that is used to fill in information in the main document. ●● The merged document, which is a combination of the main document and the mailing list. In this section, you will learn how to create a Mail Merge for letters and labels. MAIL MERGE LETTERS You can use Mail Merge to create fully customisable letters that have the same layout, formatting, text and graphics, but also have specific sections that vary and are personalised. Mail Merge will use data from a data source, like an Excel spreadsheet, to customise the sections in the letter. Before you start the Mail Merge, make sure that the mailing list is complete and up to date. If you are using an Excel spreadsheet for the data, make sure you format the cells that contain postal codes and telephone numbers as text so that you do not lose any leading zeroes. Guided activity 7.1 1. Open a blank document or an existing document. Use EntryInskryf.docx for this activity. 2. Open the Mailings tab, choose Start Mail Merge and then Letters. … continued term 3 I Chapter 7 Mailings and references I Unit 7.1 Mailings 73 Gr11_CAT_Practical_LB.indb 73 2019/09/20 11:54 AM

Guided activity 7.1 … continued 3. Select Recipients, Use an Existing List. Recipient lists can be created in a word processing table or spreadsheet. Select the spreadsheet DetailBesond to merge the documents in this activity. 4. Choose Edit Recipients List. Use any of the methods (sort, filter, etc) to edit the list. Or untick those recipients who should not receive the letter. In this case filter the list to show only Grade 11. Only the Grade 11 learners will receive the letter. … continued 74 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 74 2019/09/20 11:54 AM

Guided activity 7.1 … continued 5. Click next to Name and Surname in the document and choose Insert Merge Field. Choose the appropriate field from the list. Do this for all the fields. This is what the inserted fields would look like before the merge is completed. 6. The most important step is to complete the merge. Choose Finish & Merge, Edit Individual Documents and then choose All. … continued term 3 I Chapter 7 Mailings and references I Unit 7.1 Mailings 75 Gr11_CAT_Practical_LB.indb 75 2019/09/20 11:54 AM

Guided activity 7.1 … continued 7. After the merge has been completed, a new document is created containing personalised letters to each of the recipients in the list. Note the name of the new document (which should be changed when you save it) as well as the number of pages at the bottom of the screen. MAIL MERGE LABELS You can create personalised mailing labels using the mailing list. It is always a good idea to use the built-in label templates before you start the mail merge. Guided activity 7.2 1. Open a blank document and open Mailings. Choose Labels. … continued 76 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 76 2019/09/20 11:54 AM

Guided activity 7.2 … continued 2. Choose New label and enter the measurements as indicated. Save the new label. Do not close the blank document. Note It is possible to create a customised label by clicking on New Label. 3. Open Start Mail Merge and choose Labels. 4. Choose the label saved in Step 2. Click on OK. … continued term 3 I Chapter 7 Mailings and references I Unit 7.1 Mailings 77 Gr11_CAT_Practical_LB.indb 77 2019/09/20 11:54 AM

Guided activity 7.2 … continued The blank labels are displayed as indicated below: 5. Choose Select Recipients, Use an Existing List. Use the same spreadsheet (DetailBesond.xlsx) you used for the previous Guided Activity. Select the list from the spreadsheet. 6. Insert Merge fields. Choose Update Labels. The fields are displayed. … continued 78 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 78 2019/09/20 11:54 AM

Guided activity 7.2 … continued 7. In the same way as previously explained, the merge has to be completed. Choose Finish & Merge, Edit Individual Documents. The completed merged labels are displayed. Save and close the document. Note that the wizard can also be used to create envelopes for individually addressed recipients. term 3 I Chapter 7 Mailings and references I Unit 7.1 Mailings 79 Gr11_CAT_Practical_LB.indb 79 2019/09/20 11:54 AM

Activity 7.1 1. Open Act7.1(Letter).docx and prepare a letter mail merge as follows: a. Use the spreadsheet Act7.1(Letter).xlsx as the data source. b. Use the More items option to add a name and an address to the letter. c. Complete the merge and save the new merged document as Act7.1(Merged_letter).docx. 2. Open Act7.1(Letter).docx and prepare a label mail merge as follows: a. Use the spreadsheet Act7.1(Letter).xlsx as the data source. b. Add the merge field Address block. c. Complete the merge and save the new merged document as Act7.(Merged_labels).docx. 80 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 80 2019/09/20 11:54 AM

UNIT 7.2 References When you are creating assignments for school or university, you will need to use references to show that you are not committing plagiarism. (This means stealing someone else’s work.) You will also need to add captions to the images and tables and create a table of contents. In this section, you will learn how to insert captions, footnotes, citations, bibliographies, and content tables. TABLE OF CONTENT A table of contents helps the reader to navigate a document easily. The table of contents is used to reference where in the book a particular heading or topic is to move to that page. If Something to know the document is viewed electronically, click on the heading in the table of content and navigate straight to the relevant page. You will not be able to insert a table of contents if you have not applied heading Tables of contents are usually found at the start of a document after the title or cover page. styles to the headings. It is easiest to add them after you have written and formatted the document. Guided activity 7.3 Step 1: Ensure that all the headings have been formatted to specific Word styles, under the Home tab, Styles. Step 2: Start on a blank page after the cover page. Step 3: Select Table of Contents in the Table of Contents group in the References ribbon. Step 4: Choose one of the two Automatic Tables. Word will take all the items you have formatted into headings and add them to the table. If anything changes, you can simply click anywhere in the table and then select Update Table. term 3 I Chapter 7 Mailings and references I Unit 7.2 References 81 Gr11_CAT_Practical_LB.indb 81 2019/09/20 11:54 AM

You can also create a custom table of contents where you can set how many levels of headings you want to show, what Tab leader you want to use and even what style and fonts you would like to use. Guided activity 7.4 Step 1: Start on a blank page after the cover page. Step 2: Select Table of Contents in the Table of Contents group in the References ribbon. Step 3: Select Custom Table of Contents. Step 4: To adjust the levels displayed in the Table of Contents, open Options: Delete the heading that is not needed. Click on OK and OK again to apply the setting. … continued 82 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 82 2019/09/20 11:54 AM

Guided activity 7.4 … continued Step 5: To change the font and font size, select Modify and then select Modify again. Step 6: Change the font and font size. Select OK, then select OK again. Select OK in the main dialogue box. … continued term 3 I Chapter 7 Mailings and references I Unit 7.2 References 83 Gr11_CAT_Practical_LB.indb 83 2019/09/20 11:54 AM

Guided activity 7.4 … continued Step 7: The table of contents is complete and should look like this. Activity 7.2 1. Open Act7.2(Pangolins).docx. a. Insert a table of contents after the cover page. b. Use the default style. c. Show two levels of headings. Save document as Act7.2(TOC) and close the document. TABLE OF FIGURES A table of figures is a list of the figures in a document along with the page number of the Something to know page they are on. A table of figures is a quick reference of the captioned items in a document. You cannot create a table of Guided activity 7.5 figures before you have added captions to all the Step 1: Start on a blank page after the table of contents page. tables or figures in Step 2: Select Insert Table of Figures in the Caption group in the References ribbon. the document. … continued 84 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 84 2019/09/20 11:54 AM

Guided activity 7.5 … continued Step 3: Make any changes you need to make in the Table of Figures dialogue box that opens. Select OK when you are done. FOOTNOTES Footnotes are useful for including more information or an explanation of the text without adding more text to the main body of the document. Footnotes appear as smaller text in the Footer section of the page and are labelled with a number or letter in the body of the text. Inserting footnotes is very simple. A footnote consists of two parts. 1. The number format or symbol used to indicate that there is a footnote. 2. The footnote’s text at the bottom of the page or below the text. Guided activity 7.6 Step 1: Place the mouse cursor in the text where you want to put the footnote label. Step 2: Select Insert Footnote in the Footnotes group in the References ribbon. … continued term 3 I Chapter 7 Mailings and references I Unit 7.2 References 85 Gr11_CAT_Practical_LB.indb 85 2019/09/20 11:54 AM

Guided activity 7.6 … continued Step 3: Type the footnote in the space that is created at the bottom of the page. Step 4: To format or customise the footnotes, select the arrow in the bottom right of the Footnotes group. Step 5: Change the position, layout and numbering of the footnotes. Step 6: Select Insert when you are done. 86 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 86 2019/09/20 11:54 AM

Activity 7.3 1. Open Act7.3(Pangolins).docx. a. Find the text “Philidota” under heading 1 “Introduction”. b. Insert a footnote on the text as follows: ●● The footnote must be on the same page as the text. ●● Use the letter “a”, as the footnote symbol. ●● Add the text “From the Greek word pholís, meaning horny scale” as the footnote text. Save document as Act7.3(Footnote) and close the document. CAPTIONS Captions are labels that provide an explanation of an image, equation, object or table. They are numbered, so that a reader can refer to them in the text and so that a list of figures or tables can be created without retyping figure names and numbers. MS Word’s captions are automatically numbered, so you do not need to remember which item you are numbering. Each time that you add a new caption the other numbers will update. Guided activity 7.7 Step 1: Select the item you want to add a caption to. In this case, it is an image. Step 2: Select Insert caption in the Captions group on the References ribbon. (You can also use a right-click on the mouse.) … continued term 3 I Chapter 7 Mailings and references I Unit 7.2 References 87 Gr11_CAT_Practical_LB.indb 87 2019/09/20 11:54 AM

Guided activity 7.7 … continued Step 3: Type in the label you want for the image after the figure number. Step 4: Make sure that the Label is Figure and that the Position is Below selected item. Step 5: Select OK and then select OK again. Figure 7.1: Sir Ector kneeling before Arthur You can also change Figure to something else if you need to. The labels can be Table or Equation, or you can create your own under new label. You will use the same process in the example above to add captions to tables and equations. Table captions are usually above the table and equation captions are usually below the equation. Activity 7.4 1. Open Act7.4(Pangolins).docx. a. Add captions to the four images in the text as follows: ●● Images must use the label Figure and have a number (for example, Figure 5). b. Add a table of figures after the table of contents Save the document as Act7.4(Captions) and close the document. 88 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 88 2019/09/20 11:54 AM

CITATIONS Using references or citations when creating reports, essays or other academic work can seem intimidating, especially if you have never used them before. For most people, their first experience with citing sources only comes during tertiary education, where the consequences for not citing correctly can be very severe. Luckily, Microsoft Word has a Citation feature that helps you insert references and citations from a variety of academic referencing styles with a minimal amount of errors. Before you can insert citations, you will need to build a list of sources. You can do this for several different types of sources, including: ●● Books and book sections ●● Journal and periodical (magazines, etc.) articles ●● Conference proceedings ●● Reports ●● Websites and documents from websites ●● Electronic sources ●● Artwork, recordings, films and performances ●● Interviews, patents and cases ●● Miscellaneous sources The required fields will change depending on the source you choose. Guided activity 7.8 Step 1: Select Manage Sources in the Citations & Bibliography group in the References tab. Step 2: Select New. … continued term 3 I Chapter 7 Mailings and references I Unit 7.2 References 89 Gr11_CAT_Practical_LB.indb 89 2019/09/20 11:54 AM

Guided activity 7.8 … continued Step 3: Select the Type of Source from the drop-down menu. Step 4: Fill in the details for the source. Step 5: Check Show All Bibliography Fields if some fields you need are not shown. Something to know Some of the fields will change depending on the referencing style you choose. In this case, the fields are for the Harvard referencing style. Step 6: Select OK. Word will insert the source in the text as well as store it for use in the bibliography. Something to know Once you have added all the sources to the list, you can insert citations at the end of a paragraph or sentence as a source. Harvard and APA are the two most common Guided activity 7.9 referencing systems at South African universities, Step 1: Place the mouse cursor in the text where you want to place the citation. and they are the ones you Step 2: In the Citations & Bibliography group in the References ribbon, select Insert Citation. will most likely use. Harvard and APA are very similar in style, but they do have some differences. … continued 90 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 90 2019/09/20 11:54 AM

Guided activity 7.9 … continued Step 3: Choose the source from the list. Step 4: To add page numbers to the in-text citation, select the down arrow in the citation field and select Edit Citation. Step 5: Add the page (or page range) number in the box and select OK. Activity 7.5 1. Open Act7.5(Pangolins).docx. a. Add any five items found under the References heading (at the end of the document) as sources. ●● Make sure that you use the correct format for each source (indicated in bold and square brackets next to each source). ●● Sources must use the Harvard referencing style. b. Find the placeholders in the text for each source and add an in-text citation. Save the document as Act7.5(Sources) and close the document. term 3 I Chapter 7 Mailings and references I Unit 7.2 References 91 Gr11_CAT_Practical_LB.indb 91 2019/09/20 11:54 AM

BIBLIOGRAPHY To insert a bibliography at the end of the document, follow the steps in the example below. Guided activity 7.10 Step 1: Start on a blank page at the end of the document. Place the mouse cursor at the start of that page. (Sometimes it is the easiest to place a break after the last text to ensure you start on a new page.) Step 2: In the Citations & Bibliography select the drop-down arrow next to Bibliography. Step 3: Select the bibliography you want to use. The most common type has the heading References. Word will use all the references you have added in sources before to generate the bibliography for the document. Figure 7.2: An inserted reference list 92 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 92 2019/09/20 11:54 AM

Activity 7.6 1. Open Act7.6(Pangolins).docx. a. Insert a new bibliography under “References” on the last page. i. If the word “References” is repeated once you have inserted the bibliography, you can remove the original. ii. Remember to update the Table of Contents once the Bibliography has been added to the document. Save as Act7.6(Bibliography) and close the document. INDEX Something to know An index is an alphabetical list of names, subjects, concepts, etc. found at the end of a You cannot insert an index document with reference to the page (or pages) they are on. Indexes are generally used to or mark indexed items unless you have inserted a provide a quick reference for readers on where they can find information in the document. bibliography or list of references. Before you can add the index, you will first need to mark items for the index. Guided activity 7.11 Step 1: Turn on Show/Hide in the Paragraph group on the Home tab. Step 2: Place the mouse cursor next to the word you want to index. Step 3: Select Mark Entry in the Index group on the References tab. Step 4: Add a main and sub-entry name. … continued term 3 I Chapter 7 Mailings and references I Unit 7.2 References 93 Gr11_CAT_Practical_LB.indb 93 2019/09/20 11:54 AM

Guided activity 7.11 … continued Step 5: Select Mark. The dialogue box will stay open until you are done marking the index entries. This is what the marked index entry will look like in the document. Once you have marked all the entries, you can then add the index. Guided activity 7.12 Step 1: On a new page after the Bibliography. Step 2: Select Insert Index in the Index group. Step 3: Make any changes or adjustments as needed and select OK. Activity 7.7 1. Open Act7.7(Pangolins).docx. a. Mark the following words in the text as items for the index: i. Africa ii. Poaching iii. Scale iv. Tongue b. Insert an index after the references. Save as Act7.7(Index) and close the document. 94 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 94 2019/09/20 11:54 AM

REVISION ACTIVITY 1. Open the Chapter 7_Revision Activity 1 word processing document. Do the following: a. Set the top and right margins of the document to 2 cm. (2) b. A cover page was inserted, but all the fields were not completed: i. Move the heading highlighted in green (page 3) to the document title position. (1) ii. Display today’s date in the appropriate field on the cover page. (1) c. Create an automatic table of contents on the second page as indicated. Do the following before inserting the table of contents: i. Apply the ‘Heading 2’ style to all headings highlighted in blue. (2) ii. Modify all headings formatted as ‘Heading 3’ (highlighted in yellow) to display 7 pt before and 5 pt after each heading. (2) iii. Create a new heading style by following the instructions below: ●● Base the new style on ‘Heading 1’. ●● The new style is called ‘STEM’. ●● The font must not be expanded. (3) iv. Insert the table of contents to display all three heading levels. (3) d. Find the pictures on page 4. Do the following: i. Insert a caption below the second picture indicating the following: ‘Figure 2 SA Rural education’.(2) ii. Insert a table of figures on page 2. (2) e. Make the following changes to the document: i. Add I. Pieterse as the author of the source ‘Maths-and-science (2014)’. (2) ii. Add a citation after the last word (‘...skills.’) in the document to acknowledge the source ‘The 10 Skills Employers Most Want In 2015 Graduates’. (2) iii. Insert a bibliography at the end of the document. Make sure the format is APA. (2) 2. Open the Chapter 7_Revision Activity 2 word processing document. Do the following: A letter will be sent to the parents of the learners who scored less than 30% for either Mathematics or Physical Science. Use the data from the spreadsheet 7Marks.xls and prepare a mail merge as follows: ●● Sort the learners in ascending order according to their surnames. ●● Replace all the text between ‘<< … >>’ with the appropriate fields. ●● Only include learners who scored less than 30% for either Mathematics or Physical Science. ●● Save the document. ●● Complete the merge. ●● Save the merged document as 7Letter_Merged. (6) Total: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use Mail Merge for labels? 2 Use the referencing features to add captions, footnotes, citations, bibliographies, indexes and tables of contents? term 3 I Chapter 7 Mailings and references I Unit 7.2 References 95 Gr11_CAT_Practical_LB.indb 95 2019/09/20 11:54 AM

TERM 3 CHAPTER SPREADSHEET FORMATTING 8 CHAPTER OVERVIEW Unit 8.1: Advanced formatting Unit 8.2: Advanced formulas and functions Unit 8.3: Error indications By the end of this chapter, you will be able to: ●● Apply conditional formatting to cells. ●● Use the AutoFill options. ●● Use absolute cell referencing to keep a row or column constant. ●● Describe spreadsheet functions. ●● Apply the ROUND, SMALL, COUNTIF, COUNTA, COUNTBLANK, LARGE functions to cells. ●● Demonstrate how to round numbers. ●● Discuss the difference between rounding and formatting. ●● Describe the circular reference and #NULL! error indicators. INTRODUCTION Excel is one of the most powerful tools in the Microsoft Office Suite, and learning to use it properly is one of the best things you can do. Excel is used in a wide range of professions, from teachers who use it to calculate term marks to finance professionals who use it to keep track of complex and important financial data. Excel’s built in tools make it easier for you to do simple and complex mathematics, organise, arrange and display data, analyse data and make predictions based on that data, etc. FORMULAS IN A NUTSHELL In Grade 10 you were taught about cell references. Cell references indicate a cell or a range of cells on a worksheet so that Microsoft Excel can find the values or data you want to use in a function or formula. 96 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 96 2019/09/20 11:54 AM

Cell references can be for a single cell, for example, C14 (which refers to the cell in column C, row 14), or for a range of cells, for example, A1:F4 (which looks at all the cells from column A, row 1 to column F, row 4). While discussing cell references, you must also look at cell ranges. Cell ranges are usually a group of cells in a worksheet. They can be next to each other in a row (for example, A1:F1), below each other in a single column (for example, A1:A30) or in multiple columns and rows (for example, A1:F25). Ranges are indicated by a colon (:) in formulas. Excel calculates the formulas you enter in a specific order. Excel calculates from left to right, starting at the equals sign (=), according to a specific order for each operator. The order for this is brackets ( ), negatives (for example -2), percent (%), exponents (^), multiplication and division (* and /), addition and subtraction (+ and –). To change the order of a formula, you can use brackets around the part of the formula you want to calculate first. For example, the formula =5+2*3 will give you 11, since Excel multiplies 2 by 3 (to give you 6) and then adds the 5. Figure 8.1: Formula with no brackets But, if you use brackets to change the formula to =(5+2)*3, you will get 21, since Excel works out the sum in the bracket first (to give you 7) and multiplies that total by 3. Figure 8.2: Formula with brackets Lastly, Excel uses various error indicators to show when or where something has gone wrong in a spreadsheet: ERROR DESCRIPTION EXAMPLE INDICATOR ####### Displayed when a column is not wide enough to display all the numbers in a cell, or if the cell displays a negative time and date. #NAME? Shown when Excel does not recognise the text in a formula (if something is spelled incorrectly, for example). #DIV/0! Shown when a number is divided by zero or in a cell with no value. #REF! Displayed when a cell reference is not valid. This can happen when you delete cells that other formulas referred to. #VALUE! Displayed when the formula contains cells with different data types (letters and numbers, for example). #NUM! Shown when a formula or function has invalid numerical values, for example $1,000 will give this error, because the $ is used as an absolute reference and the comma (,) is a separator in formulas. In Excel 2014, this was replaced with the semicolon (;). term 3 I Chapter 8 Spreadsheet formatting 97 Gr11_CAT_Practical_LB.indb 97 2019/09/20 11:54 AM

UNIT 8.1 Advanced formatting CONDITIONAL FORMATTING Conditional formatting is a way to apply formatting automatically. It also helps you analyse the data. You can make worksheet data easier to interpret by using conditional formatting to format cells based on their values. If a value meets a particular condition, Excel applies the formatting; if it doesn’t, the formatting is not applied. There are many built-in options to the conditional formatting in Excel. Table 8.1 briefly describes each built-in rule. Figure 8.3: Colour scales and other conditional formatting can make it easy to quickly identify data trends Table 8.1: Conditional formatting rules RULE DESCRIPTION Highlight Cells Rules Allows you to highlight data based on certain conditions (for example, highlight all cells with a value greater than 4 000 or less than 100). You can also use this to find values in a range, highlight cells containing specific text or highlight cells containing duplicate values. Top/Bottom Rules These rules allow you to find the 10 top or bottom values, the top and bottom 10% of values or values that are over or below average. This tool is very useful for teachers who want to track class performance. Data Bars Adds a coloured bar to highlight the data in the cell. The longer the bar, the higher the value. Data bars are almost like bar graphs for each individual value. Colour Scales Colour scales use a gradient to apply background colour to a cell based on where the value of that cell falls in a range. The darker the colour, the higher the value. Icon Sets Used to add different symbols, ratings and indicators based on the values in the cell. For example, if you wanted to track monthly sales of products, you could use arrow indicators to show where sales went up, down or stayed the same. 98 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 98 2019/09/20 11:54 AM

Highlight cells Top/Bottom Data bars Colour scales Icon sets Figure 8.4: There are many built-in conditional formatting options Guided activity 8.1 In this example, we want to highlight all marks above 90 in the file GA 8_1.xlsx. Step 1: Select the data you want to highlight. … continued term 3 I Chapter 8 Spreadsheet formatting I Unit 8.1 Advanced formatting 99 Gr11_CAT_Practical_LB.indb 99 2019/09/20 11:54 AM

Guided activity 8.1 … continued Step 2: Select the Home tab, then select Conditional Formatting and then select Highlight Cells Rules. Step 3: Select Greater than. Step 4: Enter 90 into the dialogue box that opens. Keep the default formatting. Step 5: Select OK. The marks above 90 are now highlighted. You can also highlight cells that contain text. 100 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 100 2019/09/20 11:54 AM

Guided activity 8.2 Step 1: Select the data you want to highlight in the datafile GA 8_2.xlsx. Step 2: Select Conditional Formatting and then select Highlight Cells Rules. Step 3: Select Text that Contains. Step 4: Enter “soccer” into the dialogue box that opens. Keep the default formatting. … continued term 3 I Chapter 8 Spreadsheet formatting I Unit 8.1 Advanced formatting 101 Gr11_CAT_Practical_LB.indb 101 2019/09/20 11:54 AM

Guided activity 8.2 … continued Step 5: Select OK. The data will now be highlighted. You can also edit and change the existing conditional formatting in a spreadsheet by using the Manage Rules function. Guided activity 8.3 Step 1: Select the down arrow next to Conditional Formatting and then select Manage Rules. Step 2: Select the arrow next to Show formatting rules for: and select This Worksheet. … continued 102 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 102 2019/09/20 11:54 AM

Guided activity 8.3 … continued Step 3: Double click on the rule you want to edit to open the Edit Formatting Rule dialogue box and select what you want to edit. You can change the Rule Type or Edit the Rule Description. You can also change the formatting of the conditional formatting rule. Activity 8.1 Work in the Learners worksheet of Act8.1(Report).xlsx 1. Apply conditional formatting to column G so that all cells showing learners that have reported bullying: a. Appear in a light grey fill. b. Have a green border. 2. Apply conditional formatting to column D so that all cells showing learners younger than 17 appear in: a. A red fill, and learners older than 17 appear in b. a green fill with a blue border. 3. Edit the conditional formatting rules for column E so that all cells showing learners from the B group appear in: a. purple term 3 I Chapter 8 Spreadsheet formatting I Unit 8.1 Advanced formatting 103 Gr11_CAT_Practical_LB.indb 103 2019/09/20 11:54 AM

AUTOFILL OPTIONS The AutoFill option in Excel allows you to automatically fill in cells with data that follows a pattern or that is based on data in other cells. This allows you to save time when you are adding data to an Excel spreadsheet. The example below shows you how to use AutoFill in Excel. Guided activity 8.4 Step 1: Select one or more cells you want to use as a basis for filling additional cells. Step 2: For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. Step 3: Drag the fill handle down. Something to know The fill handle is the small green box in the bottom right hand corner of a cell. When you move the cursor over it, it changes from a thick white cross to a thin You can also AutoFill data horizontally. To do this, you drag the fill handle across the row (instead of black cross. down the column). Step 4: If needed, select AutoFill Options and choose the option you want. The AutoFill options button allows you to select what type of data you want to fill in the cells. These options will change depending on the data you are working with, but there are options that stay the same. Table 8.2 explains what each option is. 104 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 104 2019/09/20 11:54 AM

Table 8.2: AutoFill options OPTION DESCRIPTION Copy Cells Copies the value in a cell into the selection. Fill Series Fills in the next numbers, days or dates in a series. Fill Formatting Only Takes the formatting in a cell and fills it in the other cells in the selection, for example, the text colour or a highlight. Fill Without Formatting Fills in only the values and not the formatting from the first few cells. Flash Fill Looks at the data in the cells next to the one you selected, detects a pattern and then fills the cells with that pattern. Fill Days Fills in the names or dates of days in the range you selected. Fill Weekdays Fills in only the names or dates of weekdays (not Saturday or Sunday) in the range you selected. Fill Months Fills in the month in a date. For example, if you only want a list of the last days of the month, you will use this option. Fill Years Changes the years in a date to be consecutive years. For example, you can start the list with the date 01/11/2018, use Fill Years and have the list be 01/11/2019, 01/11/2020, 01/11/2021, etc. Activity 8.2 Open the file Act8.2(Autofill) and work on the Incident worksheet. Use AutoFill to complete the following columns for each person: ●● Column A: so that we can see how many entries there are to the worksheet ●● Column E: A unique code is created for every person Also, complete: ●● Column N: Months of the year ●● Column O: Days of the week ●● Column P: Create a custom list so when the Autofill feature is used, the nine South African Provinces are shown based on the data in the Custom List sheet. ABSOLUTE CELL REFERENCING In Excel all cell references in are relative references. This means that the reference is based on the location of the cell. For example, in Figure 8.5, the formula in Cell D2 (=B2*C2) will change to =B3*C3 if we copy it into Cell D3. Figure 8.5: No absolute referencing applied Using relative references means you do not need to retype the formula for each cell, you simply need to copy the formula from one cell to another and it will update based on where it is in the spreadsheet. term 3 I Chapter 8 Spreadsheet formatting I Unit 8.1 Advanced formatting 105 Gr11_CAT_Practical_LB.indb 105 2019/09/20 11:54 AM

Absolute cell referencing, on the other hand, lets you move a formula around on a worksheet without changing the cell references. You create an absolute reference by using the dollar sign ($) in front of the column name, row number or both. To add in the VAT (which is the same 15% for every item) in the above example, you would use an absolute cell reference which is contained in cell F1. In this case, you type in 15% in Cell F1 and then use the formula =D2*$F$1 in cell E2 to work out the total VAT for all the products in row 2. Figure 8.6: Formula with absolute referencing If you copy the formula down to cell D3, the reference to cell F1 will stay the same. Figure 8.7: Formula with absolute referencing You can also mix relative references and absolute references in a formula, by only adding the dollar symbol to one of the two values, for example: $A1 makes the column name absolute, so all information would have to come from column B B$1 makes the row number absolute, so all information would have to come from row one. Activity 8.3 Open Act8.3(Club).xlsx and work in the Music sheet. 1. Change the function in: ●● cell H2 so that it can be copied into cells H3:H17 and still give the correct results as in the current cells. ●● cell F2 so that it can be copied into cells F3:F17 and still give the correct results as in the current cells. ●● cell L2 so that it can be copied into cells L3:L17 and still give the correct results for all the years. 106 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 106 2019/09/20 11:54 AM

UNIT 8.2 Advanced formulas and functions Excel was designed to be primarily a spreadsheet program, so it is particularly powerful when it comes to calculating numbers and solving complex equations. Before you can learn how to use the functions and formulas, you will need to know the difference between the two. A formula is an expression that uses the values in a cell or range of cells to calculate the value of those cells. It is an instruction to Excel about what you want to do. All formulas begin with an equal sign (=) to let Excel know that it must do something to the data that follows it. An example of a formula is =A1+A2+A3+A4, which tells Excel to add up all the values in cells A1 to A4 and return a result. A function is a formula that is built-in to Excel. Functions perform specific calculations in a particular order based on the values in the cells and the function’s name. These are called arguments or parameters. For example, the function =SUM does what the example above does, but without you needing to add the plus sign (+). Instead, you can type in =SUM(A1:A4) and Excel will add up the values in cells A1 to A4. There are over 400 functions in Excel, but you do not need to know the name of each and every one. You can access the functions in the Function Library on the Formulas tab. Figure 8.8: The Function Library Excel also has something called Formula Intellisense, which helps you find a function based on the information you type into a cell and prompt the function’s syntax and arguments as you type. You can see this in Figure 8.9. Figure 8.9: Formula Intellisense in action In the figure above, you may notice a small text window next to SEARCH. This is the description of the function. To see this, hover the mouse cursor over the function name. term 3 I Chapter 8 Spreadsheet formatting I Unit 8.2 Advanced formulas and functions 107 Gr11_CAT_Practical_LB.indb 107 2019/09/20 11:54 AM

USING SPREADSHEET FUNCTIONS The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains the number 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND(A1, 2). The result of this function is 23.78. Guided activity 8.5 Step 1: In a blank Excel worksheet, enter the following data into column A: ●● =ROUND(3.1415, 1) to round to one decimal place. ●● =ROUND(22.25, 1) to round to one decimal place. ●● =ROUND(-6.9875, 1) to round to the nearest whole number. Step 2: If you have done this correctly, you should get the following results: You can also use ROUND with cell references, for example =ROUND(A1, 1), if you would like to have the original number and the rounded number side by side. 108 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 108 2019/09/20 11:54 AM

The difference between rounding and formatting Rounding and formatting may look the same, but they’re not. Rounding lets you get a specific value, while formatting only displays the first (x) digits of a decimal number. In everyday life, rounding is commonly used to make numbers easier to estimate, communicate or work with. For instance, you can use rounding to make long decimal numbers shorter, to report the results of complex calculations or round off currency values. Excel’s ROUND functions alter the actual value in a cell. If you want to round numbers in Excel to make the spreadsheet look neater, you can change the cell’s format. This method changes the display format without changing the actual value stored in a cell. If you refer to that cell in any formula, the original non-round value will be used in all calculations. The SMALL and LARGE functions do similar things. They return the smallest or largest value (based on a condition that you set) from a set of values. This means that in a range of numbers, you can specify that you’re looking for the fourth or second smallest or largest number. For example, if you have a list of numbers in cells A1 to A16, you can enter the formula =SMALL(A1:16, 4) into cell B17 to get the fourth smallest number in the range and the formula =LARGE(A1:A16, 2) in cell B18 to get the second largest number. You can see this in Figure 8.10. EXAM TIP Always keep the data in mind when you are applying LARGE and SMALL. For example, if you are looking for the fastest lap time, it will be the smallest number. Figure 8.10: SMALL and LARGE applied to a range of numbers term 3 I Chapter 8 Spreadsheet formatting I Unit 8.2 Advanced formulas and functions 109 Gr11_CAT_Practical_LB.indb 109 2019/09/20 11:54 AM

The POWER function returns the result of a number raised to a power. For example, if you have the value 2 in cell A1 and you want to know what the result will be if you raised it to 10, you could enter the formula =POWER(A1, 10) into cell B1 to get the answer, or 1 024. You can also use a caret (^) instead of the POWER function. Figure 8.11: Using a caret instead of the POWER function THE COUNT FUNCTIONS COUNT counts cells containing numerical values. COUNTA counts cells containing any type of information. For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells. COUNTBLANK counts the number of empty cells in a range. Activity 8.4 Open Act8.4(Rain).xlsx and work in the Rain spreadsheet. 1. Insert a function in cell G48 to determine the total rainfall for April. 2. Insert a function in cell H49 to determine how many times no rain fell in May. 3. Insert a function in cell K50 to determine the highest rainfall in August. 4. Insert a function in cell M51 to determine the lowest rainfall in October. 110 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 110 2019/09/20 11:54 AM

UNIT 8.3 Error indicators Error indicators are Excel’s way of letting us know that we did something incorrectly when entering a formula and that our logic does not make sense. In Grade 10, you were taught about #######, #NAME?, #DIV/0!, #REF!, #VALUE! and #NUM!, which are all used to alert you that the values in the formula or the formula’s parameters are incorrect. In this unit, you learn about two new error indicators, namely circular references and #NULL!. CIRCULAR REFERENCE A circular reference error happens when the formula you entered is trying to calculate a value in the cell you put it in, which means that the formula keeps going in circles, almost like a dog chasing its own tail. You can see an example of this error message in Figure 8.12. Figure 8.12: The circular reference error message To see how this works, look the box below. Circular reference error Frank wants to calculate a range of numbers from cell A1 to cell A10, but he makes a mistake and enters the formula =SUM(A1:A11) in cell A11. As soon as he presses the Enter button, the circular reference error message pops up. term 3 I Chapter 8 Spreadsheet formatting I Unit 8.3 Error indicators 111 Gr11_CAT_Practical_LB.indb 111 2019/09/20 11:54 AM

HOW TO FIX CIRCULAR REFERENCE ERRORS Circular reference errors are relatively simple to fix. All you need to do is go to the formula and check it very carefully. Guided activity 8.6 Step 1: Select OK in the error message that pops up. Step 2: Go to the formula you were trying to calculate and check the cell references. Step 3: Change the incorrect cell reference to the correct one. In this case, change A11 to A10 to get the correct answer. … continued 112 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 112 2019/09/20 11:54 AM

Guided activity 8.6 … continued Step 4: Press Enter to let Excel calculate the sum. #NULL! #NULL! errors happen in one of two cases: 1. When you use an incorrect range operator in a formula, for example, using a semi- colon (;) instead of a colon (:) to separate cell ranges. 2. When you use a space between range references to specify an intersection between two ranges that do not intersect. An intersection in Excel is a point in the spreadsheet where the data in two or more ranges cross. The reference operators in Excel are the: ●● Range operator (colon or :) which defines the reference to a range of cells. ●● Union operator (comma or ,) that combines to references into one reference. ●● Intersection operator (a blank space) that indicates the intersection of two ranges. ●● You can see an example of a #NULL! error in Figure 8.13. Figure 8.13: A #NULL! error As you can see, the range operator (:) has been left out of the sum. term 3 I Chapter 8 Spreadsheet formatting I Unit 8.3 Error indicators 113 Gr11_CAT_Practical_LB.indb 113 2019/09/20 11:54 AM

HOW TO FIX #NULL! ERRORS You need to check that you are using the correct syntax in the formula. This means that you must make sure that you are using a colon when you are referencing a range of cells that follow on from each other (for example A2:A36 or A1:R1) or a comma to reference cells that need to be combined (for example A1:A25,A36 or A1:L1,M1). Activity 8.5 Open Act8.5(Data Errors).xlsx. Select OK on the error message that opens. 1. Determine why the #NULL! error is in all the cells and correct it. 2. Correct the circular references. REVISION ACTIVITY Open the Chapter 8_Revision Activity spreadsheet. Work in the Travellers worksheet. The worksheet shows the statistics of the arrival and departures of travellers using the King Shaka airport from 2010 to 2014. 1. Format the cells in row 1 as follows: a. Merge the text in row 1 from cells A1:I1. (1) b. Change the row height of row 1 to 57. (1) c. Change the size of the heading in row 1 to 22 pt. (1) 2. Adjust the column width of rows 2 & 3 so that all the subheadings are clearly visible. (1) 3. Column C has the arrival months of the travellers. Use AutoFill to continue the arrival months. (1) 4. Apply conditional formatting to column D so that all the cells displaying more than 4000 SA arrivals:(3) a. Appear in a light blue fill. b. Have a red border. 5. In cell D54 calculate the number of SA residents that arrived at the Airport. (2) 6. In cell L4 determine the second largest number of foreign travellers that have departed from the airport.(2) 7. In cell L5 determine the least number of SA residents that have departed from the airport. (2) 8. Use a function in cell L6 to determine how many visitors overnighted in May. (2) Work on the Foreign worksheet. The spreadsheet contains the number of nights spent in each province by foreign tourists. 9. Change only the vertical borders between the headings in row 2 to a reddish line. (2) 10. Insert a function in cell L3 to display the average number of ALL Malawian visitors to South Africa.(3) 11. Insert a function in cell L4 to show how many provinces did not receive Mozambican visitors who came by air. (2) 12. Use conditional formatting to automatically display from which 8 countries the most travellers to KwaZulu Natal came. Fill the background of these cells with any background colour of your choice. (3) 13. Use a function in cell B50 to calculate all the foreign tourists who visited Gauteng. (2) 14. Insert a function in cell L5 that will display how many countries had people visiting North West. (2) Total: [30] 114 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 114 2019/09/20 11:54 AM

AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Apply conditional formatting to cells? 2 Use the AutoFill options? 3 Use absolute cell referencing to keep a row or column constant? 4 Describe spreadsheet functions? 5 Apply the ROUND, SMALL, COUNTIF, COUNTA, COUNTBLANK, LARGE functions to cells? 6 Demonstrate how to round numbers. 7 Discuss the difference between rounding and formatting? 8 Describe the circular reference and #NULL! error indicators. term 3 I Chapter 8 Spreadsheet formatting I Unit 8.3 Error indicators 115 Gr11_CAT_Practical_LB.indb 115 2019/09/20 11:54 AM

TERM 3 CHAPTER IF FUNCTIONS AND CHARTS 9 CHAPTER OVERVIEW Unit 9.1: Simple IF function Unit 9.2: Relational operators Unit 9.3: Charts By the end of this chapter, you will be able to: ●● Use simple IF functions to solve problems. ●● Use SUMIF and COUNTIF. ●● Describe the use of relational operators in simple IF statements. ●● Create and interpret charts and graphs in Excel. ●● Format and edit charts and graphs in Excel. ●● Embed charts and graphs into Microsoft Word. INTRODUCTION The IF function is one of the most used functions in Excel. IF statements allow you to compare conditions in a worksheet. Simply put, IF statements allow you to say that if one condition is true, then the formula should do something. If it is not, the formula should do something else. IF statements can return text, values or even more calculations. One of Excel’s most useful visual tools are the charts and graphs you can create using data. Charts and graphs help you create a visual representation of data that makes it easier for others to interpret and analyse the data. In this chapter, you will learn about simple IF functions and how to use them as well as how to create charts using data in Excel. 116 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 116 2019/09/20 11:54 AM

UNIT 9.1 Simple IF function The IF function is built-in to and is one of the Logical Functions in Excel, which means that it follows the logic of IF something is true, then do this, else/otherwise do that. It can be entered as part of a formula in a cell of a worksheet or it can stand alone in a cell. IF statements are usually written with the following syntax: IF (condition, value_if_true, [value_if_false]) Something to know You can work along with Where the condition is the value you want to test, the value_if_true is the value that the these examples in a blank function gives you if the condition is true and the value_if_false is the value that the function Excel spreadsheet. returns if the condition is false. Guided activity 9.1 Step 1: Enter the number 60 in cell A1. Step 2: In cell B2, enter the formula =IF(A1=60, “Pass”, “Grounded”). This says that if A1 is 60, enter “Pass”. If it is not, enter “Grounded”. Step 3: Press Enter to see the result. Let us look at another example of an IF statement in action. Guided activity 9.2 Step 1: Enter the word rain in cell A1. Step 2: In cell B2, enter the following IF statement =IF(A1=”rain”, “umbrella”, “cap”) Step 3: Press Enter to see the result. term 3 I Chapter 9 IF functions and charts I Unit 9.1 Simple IF function 117 Gr11_CAT_Practical_LB.indb 117 2019/09/20 11:54 AM

Activity 9.1 Open Act9.1(Learners).xlsx and work in the Members worksheet. An email must be sent to every person who does NOT have a cell phone number (Column G). 1. Insert a function in cell I3 to determine if an email needs to be sent. If an email must be sent, “Yes” must be displayed, otherwise “No” needs to be displayed. Copy this function to cells I4:I101 2. Every member who joined in 1998 (Column C) qualify for a discount in membership fees. Insert a function in cell J3 to determine which members qualify for a discount. If a member qualifies, “Qualify” must be displayed, otherwise “Not qualify” needs to be displayed. Copy this function to cells J4:J101 3. Members who paid on the 25th day or later (Column E) will be fined for late payment. Insert a function in cell K3 to determine if a member must be fined. If the entry shows the day 25 or later, “Fined” must be displayed, otherwise “No Fine” needs to be displayed. Copy this function to cells K4:K101 SUMIF The SUMIF function lets you sum in one range based on a specific criterion you look for in another range. This means that you can use the values in a range to modify the values in another range. Guided activity 9.3 Open the spreadsheet GAct 9.3.xlsx. Calculate the total sales of Vodacom airtime only. Step 1: In cell D2, enter the formula =SUMIF(B2:B10,”Vodacom”,C2:C10) Step 2: Press Enter to see the result. 118 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 118 2019/09/20 11:54 AM

To make sure that you do not make logic or argument mistakes in functions like SUMIF, you can use the Function Builder in Excel. This tool gives you tips and guidance on how to build functions and keep arguments in the correct order. Guided activity 9.4 Open the spreadsheet GAct 9.3.xlsx. Using our data from the previous example, we want to calculate all CellC data sales. Step 1: Select cell D2. Step 2: Select the Function Builder icon next to the Formulas bar. Step 3: Type a brief description of what you want to do, select a category or select a function to find the function you want to use. Step 4: Select the function you want to use (SUMIF) and then select OK. Step 5: Begin filling in the function arguments and then select OK. To select the range, you can return to the sheet by selecting on the arrow next to the Input bar. Select the range and press enter to return to the Function Builder. … continued term 3 I Chapter 9 IF functions and charts I Unit 9.1 Simple IF function 119 Gr11_CAT_Practical_LB.indb 119 2019/09/20 11:54 AM

Guided activity 9.4 … continued Step 6: See the result in cell D2. Activity 9.2 Open Act9.2(Sales).xlsx and work in the Data worksheet. 1. Insert a function in cell I3 to determine the total value (Column H) of all the products sold by the 7Digital Store (in column A). Remember to format the answer as Currency. 2. Insert a function in cell I6 to determine the total value (Column H) of all the MP3s (in column A) sold. Remember to format the answer as Currency. 3. Insert a function in cell I9 to determine the total value (Column H) of all the products sold by the artist Green Day (in column D). Remember to format the answer as Currency. 4. Insert a function in cell I12 to determine the total value (Column H) of all the products sold by the Label EMI UK (in column E). Remember to format the answer as Currency. 5. Insert a function in cell I15 to determine the total value (Column H) of all the DVDs sold (in column B). Remember to format the answer as Currency. COUNTIF The COUNTIF function counts the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list. In its simplest form, COUNTIF says: =COUNTIF(Where do you want to look?, What do you want to look for?). For example: =COUNTIF(A2:A5,”London”); =COUNTIF(A2:A5,A4) Activity 9.3 Open Act9.3(sales).xlsx. 1. Insert a function in cell E3 to determine how many sales were made by Williams (in column A). 2. Insert a function in cell E6 to determine how many sales were made by Jones (in column A). 3. Insert a function in cell E9 to determine how many sales were made in Qtr. 2 (in column D). 120 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 120 2019/09/20 11:54 AM

UNIT 9.2 Relational operators You can use relational operators (>, <, <=, >= and <>) in simple IF functions. These will help you refine the data even more. Table 9.1 gives a brief explanation of each relational operator. Table 9.1: Relational operators in Excel OPERATOR DESCRIPTION > Greater than. Used to determine if one value is larger than another. < Less than. Used to determine if one value is lower than another. Less than or equal to. Used to determine if one value is less than or the same as <= another value. Greater than or equal to. Used to determine if one value is more than or the same as >= another value. <> Not equal to. Used to show that two values are not equal to each other. Guided activity 9.5 Mr Tshabalala wants to see if his students may play on the soccer team. They may only play if they have less than 20 demerit points. Open the spreadsheet GAct 9.5.xlsx. Step 1: Mr Tshabalala enters the formula =IF(C2>20,”No”,”Yes”) in cell D2. This says that if the student has more than 20 demerit points, they may not play on the soccer team. Step 2: He presses Enter to view the result. Step 3: He then copies the formula down to check the rest of the data. You can use relational operators in IF statements to force additional calculations to be performed if a certain condition is met. term 3 I Chapter 9 IF functions and charts I Unit 9.2 Relational operators 121 Gr11_CAT_Practical_LB.indb 121 2019/09/20 11:54 AM

Guided activity 9.6 Open the spreadsheet GAct 9.6.xlsx. A school administrator wants to check how many Grade 10 learners can be classified as teenagers (younger than 16) or young adults (16 years old and older). He takes the class list of all Grade 10 learners in his school and does the following: Step 1: In cell C2, he enters the formula =IF(B2>=16,”Young adult”, “Teenager”). This states that if the learner is older than or is 16, they are classified as a young adult. Step 2: She presses Enter to see the result. Step 3: She copies the formula down to check the rest of the data. While the calculations in these activities could be done manually (since the amount of data was small), it would be very time consuming to do this with a larger set of data. Imagine if the worksheet had 4 000 entries. It would be labour intensive to go through all of the entries and check each one. This is why using IF statements are very useful since the statements will check the entries for you. 122 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 122 2019/09/20 11:54 AM

Activity 9.4 Open Act9.4(Dublin).xlsx. Work in the List worksheet. 1. The organisers of St Patrick’s Day want to know with which currencies they will be dealing with, whether visitors will contribute pound sterling, dollars or Yen. a. Insert a function in cell G3 to determine which visitors in Column F are from the UK. If they are from the UK the word “Pounds” must be displayed, otherwise “No Pounds” must be displayed. Copy this function to the cells in Column G for all visitors. b. Insert a function in cell H6 to determine which visitors in Column F are from the USA. If they are from the USA the word “Dollars” must be displayed, otherwise “No Dollars” must be displayed. Copy this function to the cells in Column H for all visitors. c. Insert a function in cell I4 to determine which visitors in Column F are from the Japan. If they are from the Japan the word “Yen” must be displayed, otherwise “No Yen” must be displayed. Copy this function to the cells in Column G for all visitors. Work in the Summary worksheet d. Insert a function in cell C2 to display “True” if the total number of visitors per city (Column B) is 4 visitors or more. Otherwise, the function must display “False”. Copy the function to indicate for all cities’ visitors up to cell B21. e. Insert a function in cell D2 to display “True” if the total number of visitors per city (Column B) is less than 8. Otherwise, the function must display “False”. Copy the function to indicate for all cities’ visitors up to cell B21. term 3 I Chapter 9 IF functions and charts I Unit 9.2 Relational operators 123 Gr11_CAT_Practical_LB.indb 123 2019/09/20 11:54 AM

UNIT 9.3 Charts Charts are especially important when you are presenting data in a report. In this unit, you will learn how to create charts and graphs, how to format them and how to edit them. In Mathematics and Maths Literacy, you learned about graphs. In Excel, we call these charts. In this unit, we will refer to them as charts. CREATING CHARTS Charts can only be created when the data is in the sheet and is sorted in a logical way. In Excel, charts are under the Insert tab in the Illustrations group. Guided activity 9.7 Step 1: Select data for the chart. Step 2: Select the Insert tab. Step 3: Select Recommended Charts. … continued 124 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 124 2019/09/20 11:54 AM

Guided activity 9.7 … continued Step 4: Select a chart on the Recommended Charts tab to preview the chart. Step 5: Select the chart that will work best for the data. Step 6: Select OK. The chart or graph will be added to the Excel spreadsheet. You can then move the chart around, resize it or edit it. Activity 9.5 1. Open Act9.5(Species).xlsx and work in the Spec worksheet. a. Create a pie chart in a new worksheet called Pie Chart to display the severity percentages for all Vertebrates (Column A). ●● Add the title “% of Endangered Vertebrates” roughly in the middle of the chart. ●● Add the legend at the bottom. ●● You must use data labels for each piece of the pie chart. ●● The “Birds” section must be separated from the rest of the pie. b. Create a column graph in a new worksheet called Column Chart to display the number of threatened species per year (2008 – 2014) for all Invertebrates (Column A). ●● Add an appropriate title to the chart. ●● Add the legend at the top. ●● You must use data labels for the chart. term 3 I Chapter 9 IF functions and charts I Unit 9.3 Charts 125 Gr11_CAT_Practical_LB.indb 125 2019/09/20 11:54 AM

FORMATTING AND EDITING CHARTS AND GRAPHS When it comes to finally displaying the data, there are many formatting options you can choose from to make sure the data is displaying in a way that gets the message across in the most effective way. There are two main ways that you can format a chart: ●● Using the Format task pane. ●● Using the Chart Tools ribbon. The Format pane allows you to format the data display elements of the chart, like the axes, the data series, the title, etc. The Chart Tools ribbon allows you to format the colour, shapes and text. Guided activity 9.8 Step 1: Right-click on the chart element you want to format. This could be the axes, data series or titles, for example. Step 2: Select Format [chart element]. In this case, it’s the axis. Step 3: The Format pane opens on the right of the screen with options for the element. … continued 126 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 126 2019/09/20 11:54 AM

Guided activity 9.8 … continued Step 4: Using this window, you can change the label position of the axes, change the number format, add tick marks to the axes or adjust the axes themselves. In the Format pane, you can format the chart element using the following tools: ●● Fill & Line lets you change the fill colour and border type for each element. ●● Effects lets you add image effects (like shadows or a glow) to the element. ●● Size & Properties lets you change the width, height and scale of the element as well as assign it certain properties. You can change the chart’s layout, style and chart type as well as adding shapes and text effects to the chart using the Chart Tools ribbon. There are several things that you can format using the ribbon. On the Design tab, you can: ●● Add new chart elements. ●● Change the layout using Quick Layout. ●● Change the colours and styles. ●● Use the Data group, you can switch the rows and columns and change the data range. ●● Change the chart type and move the chart to another sheet or tab in the workbook. Figure 9.1: The Design tab of the Chart Tools ribbon Using the Format tab, you can: ●● Format the element you have selected. ●● Insert shapes and change the styles of those shapes. ●● Add WordArt and change the styles. ●● Rearrange the chart’s position in the spreadsheet. ●● Change the size of the chart. Figure 9.2: The Format tab of the Chart Tools ribbon You can also add or remove gridlines, legends, data labels, titles and other options by using the Chart Elements button. term 3 I Chapter 9 IF functions and charts I Unit 9.3 Charts 127 Gr11_CAT_Practical_LB.indb 127 2019/09/20 11:54 AM

Guided activity 9.9 Step 1: Select the chart. Step 2: Select the Chart Elements button. Step 3: Tick or untick elements you want to add or remove from the chart. Step 4: Remove the gridlines from a chart by unticking the Gridline box. Step 5: Add a legend to the chart by ticking the Legend box. Legends are useful when you have more than one bar or data element in a chart since they let you reference the chart more easily. 128 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 128 2019/09/20 11:54 AM

GIVING CHARTS AND GRAPHS MEANINGFUL TITLES AND LABELS The most important thing to think about when creating a chart is that the chart should give the reader all the information they need to quickly and easily interpret the data without them needing to interact with the data in any way. This is why you need to make sure that the chart’s titles and labels are meaningful and carry the appropriate message across. When you create a chart, think carefully what you will call the chart. It’s not enough to call it “Marks” when the chart is actually showing different English class marks. Rather call it “Term 3 English Marks” to convey the appropriate and relevant message. Naming the axes is also important, since people need to know that they are looking at. If you just have rows and columns of numbers without explaining them, the people looking at the chart might not be able to interpret it correctly. For example, if the y-axis (the line on the left going up) shows how many people attended an event, call that axis “Attendance”. If the x-axis (the flat line on the bottom) shows dates for the event, call that axis “Event dates”. Guided activity 9.10 There are two ways to add chart titles and labels. Method 1: Adding a title directly Step 1: In the chart, select the Chart Title box and type in a title. Step 2: Select the Chart Elements button. Step 3: Select the arrow next to Chart Title. Step 4: Select Centered Overlay to lay the title over the chart or use More Options to get extra choices. … continued term 3 I Chapter 9 IF functions and charts I Unit 9.3 Charts 129 Gr11_CAT_Practical_LB.indb 129 2019/09/20 11:54 AM

Guided activity 9.10 … continued Step 5: Right-click the chart title to get options like Fill or Outline to format the chart. Method 2: Linking a title to a cell Step 1: In the chart, select the Chart Title box. Step 2: Type = and select the cell that contains the text you want to use as a chart title. Step 3: Press Enter. EDITING CHARTS AND GRAPHS Editing charts is actually very simple as long as you remember that whatever you do to the data, you do to the chart. This means that if you make a change to the source data that the chart draws from, the chart will update automatically to reflect that. There are three main things you can edit in a chart: 1. The data itself 2. The data series 3. The titles In this section, you will learn how to edit the titles and data series of the chart. If you have any titles (like a chart or axis title) or labels (specifically data labels) in the chart, you can edit them in the chart itself. You can also use rich text formatting to make changes to the titles and labels, by adding colour, text effects and more. Note that if you have linked the chart title or label to a cell in the worksheet, you will make the changes in that cell. Guided activity 9.11 Step 1: To edit: ●● A title, select the chart or axis title that you want to change. ●● A data label, click twice on the data label that you want to change. ●● (The first click selects the data labels for the whole data series, and the second click selects the individual data label.) Step 2: Click again to put the title or label in editing mode. Step 3: Select the text that you want to change and type the new text or value. Step 4: When you are finished editing, click outside of the text box where you have made text changes. Guided activity 9.12 Step 1: Right-click on the chart, and then choose Select Data. You can also get to this dialog box from the Value tab in the Chart Filters gallery. 130 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 130 2019/09/20 11:54 AM

REVISION ACTIVITY Ace Travel has stored a list of their clients in a spreadsheet. Open the Chapter 9_Revision Activity spreadsheet and work in the Clients_Kliënte worksheet. 1 Format the worksheet as follows: a. Centre the text in row 1. (1) b. Use a light blue fill colour for row 1. (1) c. Change the height of row 1 to 30pt. (1) d. Wrap the contents of cell A3. (1) 2. Use conditional formatting on column E to automatically highlight all males in a yellow fill colour and red text colour. (3) 3. Insert a function in cell K8 to determine the total number of trips made by clients. (2) 4. Insert a function in cell K9 to determine how many clients are NOT international travellers. (3) 5. Create a pie chart to display the percentage breakdown of each category of traveller status. Use the data in cells J11:K13 to create the chart as follows: ●● Add the following chart title above the chart (bold & 14pt): Traveller Status ●● Display the percentage for each category of traveller status. ●● Fill the category that has the highest percentage with any gradient colour of your choice. ●● Display the chart in a new worksheet and rename it to: Travellers (8) Work in the Bookings_Besprekings worksheet. 6. The rate per day is stored in column H. Insert a formula in cell D5 to determine the total accommodation cost for the duration of the stay for Bertha Hood. (2) 7. Insert a function in cell J7 to determine the third lowest rate per day. (2) 8. Insert a function in cell J10 to determine the total number of days (column C) that clients will spend at hotels or lodges in Kenya. (4) 9. A booking reference in column A that is indicated with an ‘X’ indicates an international hotel or lodge. A booking reference that is indicated with a ‘D’ indicates a domestic hotel or lodge. Insert a function in cell E10 to return the value ‘TRUE’ if the hotel or lodge in row 10 is international and the value ‘FALSE’ if the hotel or lodge in row 10 is domestic. (4) Total: [32] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Use simple IF functions to solve problems? 2 Use SUMIF and COUNTIF? 3 Describe the use of relational operators in simple IF statements? 4 Create and interpret charts and graphs in Excel? 5 Format and edit charts and graphs in Excel? 6 Embed charts and graphs into Microsoft Word? term 3 I Chapter 9 IF functions and charts I Unit 9.3 Charts 131 Gr11_CAT_Practical_LB.indb 131 2019/09/20 11:54 AM

TERM 3 CHAPTER WORKING WITH SPREADSHEETS 10 CHAPTER OVERVIEW Unit 10.1: Import/export data Unit 10.2: Help files Unit 10.3: Manipulating worksheets Unit 10.4: Integration techniques By the end of this chapter, you will be able to: ●● Import data into Excel from various sources. ●● Export data from Excel into various formats. ●● Manipulate spreadsheet data, including moving and copying data, adding headings, freezing panes and viewing gridlines. ●● Protect a spreadsheet using Excel’s built-in tools. ●● Use different print options, including print area. ●● Link cells and formulas between sheets. INTRODUCTION Excel’s ability to take large sets of data and perform complex calculations on it has made it one of the most popular spreadsheet programs in the world. This popularity is also due to the fact that Excel makes it easy for users to import data from other sources and to manipulate that data so that it makes sense. WORKING WITH EXCEL You can make a workbook easier to read and more presentable by inserting headers and footers. A header is found in the top margin and the footer is found in the bottom margin. To insert a header or footer: ●● Navigate to the bottom right hand side of the workbook. Select the Page Layout view command. The worksheet will then be shown in the Page Layout view. ●● Select the header or footer that you want to format. In this example we will modify the header at the top of the page. 132 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 132 2019/09/20 11:54 AM

●● The Header & Footer tools tab will appear in the Excel ribbon. From here, you can add page numbers, dates, workbook names and so on. The header will change and then automatically add page numbers. You may also need to format or update the data type of a particular cell. To do this, you will: 1. Select the cell or cells that you want to modify. 2. Select the drop-down arrow in the Numbers group on the Home tab. 3. A dialogue box will open; from there you can select the data type that you want. 4. Lastly, select OK and the data type will be applied to the selected cells. term 3 I Chapter 10 Working with spreadsheets 133 Gr11_CAT_Practical_LB.indb 133 2019/09/20 11:54 AM

UNIT 10.1 Import/Export data Not all the data you will work with in Excel will be in Excel to begin with. You may need to import data from an external source into Excel in order to work with it properly. You may also need to export the data from Excel to another program so that you can share it with people who may not have access to Microsoft’s Office products. In this unit, you will learn how to import data from an Access database, a CSV file and from another Excel workbook. You will also learn how to export Excel data to a CSV file. IMPORTING DATA Imagine for a moment that you have been given a Notepad file containing rows and rows of data that needs to be input in Excel so that it can be sorted and analysed. If you were to try doing this manually, it could take days to do and you may make mistakes or leave something out. Luckily, Excel’s import function will allow you to quickly import the data without any mistakes. In your future careers, you may need to analyse data from a variety of sources, a Microsoft Access Database, a text file or a SQL file, for example. IMPORTING DATA FROM ACCESS When you are importing data from Access, you may need to take information from more than one table. Excel allows you to select the tables in an Access database when you are importing the data. To see how to import data from Access to Excel, read through the following guided activity. Guided activity 10.1 Step 1: Open a blank workbook. Step 2: Select the Data tab. Step 3: Select From Access. … continued 134 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 134 2019/09/20 11:54 AM

Guided Activity 10.1 … continued Step 4: Select the Access file you would like to import data from and select Open. Access files end in *.accdb. Step 5: Use the Select Table dialogue box to choose which tables you want to import. Something to know If you want to select more than one table, make sure Enable selection of multiple tables is checked. Step 6: Choose how you want to view the data in the Import Data window. In this example, we selected Table. Step 7: Select OK and wait for the data to be loaded. This may take a few seconds, depending on the size of the database. … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.1 Import/Export data 135 Gr11_CAT_Practical_LB.indb 135 2019/09/20 11:54 AM

Guided Activity 10.1 … continued Step 8: As you can see, the data has been imported as tables on separate worksheets. Step 9: The data is now imported and ready for you to work with. IMPORTING DATA FROM A TEXT FILE Very often, data that you download or extract from websites or online databases will be in a text file (*.txt). Reading this data in that file can be extremely difficult and it may be nearly impossible to analyse it or draw any conclusions from the data. In the following guided activity, you will see how data from a text file can be imported into Excel. Guided activity 10.2 Step 1: Open a blank workbook. Step 2: Select the Data tab. Step 3: Select From Text. … continued 136 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 136 2019/09/20 11:54 AM

Guided activity 10.2 … continued Step 4: Select the text file, Import.txt, to import data from and select Import. Text files end in *.txt Step 5: Use the Text Import Wizard to select the format that best describes the data. Step 5a: In this case, we selected Comma Delimited and made sure that My data has headers has been ticked. Select Next. Step 5b: Select the delimiters of the data. Here it is Comma. Select Next. … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.1 Import/Export data 137 Gr11_CAT_Practical_LB.indb 137 2019/09/20 11:54 AM

Guided activity 10.2 … continued Step 5c: Select the format for the data. You can select each column and choose the Data Format for that column. Once you are done, select Finish. Step 6: The Import Data dialogue box will open. Select how you want to view the data. In this case, Table was automatically selected. You can also choose where you want the data to start in the worksheet. We chose cell A1. Select OK when you are done. Step 7: Check that the data has imported correctly and that the columns are correct. Step 8: The data is now imported and ready to work with. 138 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 138 2019/09/20 11:54 AM

You can also simply copy and paste the data from one workbook to another. Once you have all the data you need in one place, you can compare the data or create relationships between the data in one sheet and the data in another sheet. EXPORTING DATA When you have finished manipulating the data in Excel, you may need to export it to a different file format. This could be because you want to upload it to a SQL database, and you need it to be a comma separated values (CSV) file or the people you are sharing the data with do not have access to Excel. The simplest way to export data to a different file format is to use the Save As function and change the file to a CSV. EXPORTING TO CSV To export the data in an Excel spreadsheet to CSV, you simply need to use the Save As function. Guided activity 10.3 Step 1: Select File. Step 2: Select Save As. … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.1 Import/Export data 139 Gr11_CAT_Practical_LB.indb 139 2019/09/20 11:54 AM

Guided activity 10.3 … continued Step 3: Select the location you want to save the file to. Step 4: Select CSV (Comma delimited) (*.csv) from the Save as type dropdown menu in the Save As dialogue box. … continued 140 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 140 2019/09/20 11:54 AM

Guided activity 10.3 … continued Step 5: Change the name from Book1 to the document’s new name and select Save. Step 6: Select Yes in the message box that pops up. Step 7: Close the workbook. Activity 10.1 Open Act10.1(Species).xlsx. 1. Import the data from Act10.1(Imp).csv into the worksheet Imp. 2. Save and close the spreadsheet. term 3 I Chapter 10 Working with spreadsheets I Unit 10.1 Import/Export data 141 Gr11_CAT_Practical_LB.indb 141 2019/09/20 11:54 AM

UNIT 10.2 Help files To access help in Microsoft Excel, you can do any of the following four things: 1. Enter a query in the box labelled Tell me what you want to do at the end of the tabs at the top of the document. 2. Press F1 on the keyboard to enter the Help panel on the right-hand side of the document. 3. Select File and then select the ? in the top right-hand corner. 4. Select the Help tab. 142 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 142 2019/09/20 11:54 AM

UNIT 10.3 Manipulating worksheets Knowing how to manipulate the data in the worksheets correctly is an essential skill when working with Microsoft Excel. If you do not move the data correctly, this could lead to errors in the results or the formulas not working correctly. In this unit, you will learn how to work with Excel spreadsheets and how to move and copy data, how to add headings and freeze panes, how to view gridlines and how to protect the spreadsheet. You will also learn about different print options available to you when you want to print it. WORKING WITH SHEETS When you move or copy a cell, Excel will keep that cell’s formulas and the results, the formatting and any comments. MOVING AND COPYING DATA If you only want the resulting values of a formula to be moved. You can drag and drop cell data to move data. Guided activity 10.4 Step 1: Select the cells you want to move. Step 2: Move the mouse cursor to the border of the selection and wait for it to become the move pointer. … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.3 Manipulating worksheets 143 Gr11_CAT_Practical_LB.indb 143 2019/09/20 11:54 AM

Guided activity 10.4 … continued Step 3: Drag the selection to another part of the worksheet. Another option for moving data is to use the Cut and Paste commands. Sometimes, you might have to copy the results of a formula without copying the formula itself. Remember that when Excel copies a cell it copies all of the data in that cell. To copy just the values to a new area, you will need to use the Paste Special feature in in Excel. Paste Special in Excel has more options than in Microsoft Word. In the example below, you will learn how to use the most commonly used option, pasting values only. Guided activity 10.5 Step 1: Select the data you want to copy. As you can see, there are functions in these cells. Step 2: Copy the data using the Copy command in the Home ribbon or by pressing Ctrl+C. … continued 144 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 144 2019/09/20 11:54 AM

Guided activity 10.5 … continued Step 3: Right-click in the cell you want to move the data to. An Options dialogue box will open. Step 4: Select the Paste Values icon. Step 5: The data will be pasted as values only. As you can see, when the data is selected, the functions have been removed. MOVE AND COPY CELLS AND THEIR CONTENT https://www.youtube.com/ watch?v=u1rp9nzLQw8 There are many other moving and copying options available in Excel. To find out more, visit the Microsoft Support website by scanning the QR code on the right. VIEWING GRIDLINES AND FREEZING PANES Often, the way the data is displayed can make it easier or harder to read. This is especially true when you have many rows of data that you need to scroll down to see and the title rows disappear. You can change the way the gridlines are displayed on a worksheet and you can also freeze certain panes in a worksheet to make scrolling through the sheet easier. Guided activity 10.6 Excel has the gridlines in every workbook on by default. To turn the gridlines on and off, you can do the following: Step 1: Go to the View tab. Step 2: Tick or untick the Gridlines box in the Show group. term 3 I Chapter 10 Working with spreadsheets I Unit 10.3 Manipulating worksheets 145 Gr11_CAT_Practical_LB.indb 145 2019/09/20 11:54 AM

When you have a lot of data in a workbook and do not want to lose track of what each column or row contains, you can freeze certain parts of the document so that they stay in place while you scroll. You can choose to freeze the top row of data, the first column of data or rows and columns of your choice. To do this, follow the steps below. Guided activity 10.7 Step 1: Select the View tab. Step 2: Select Freeze Panes in the Window group. Step 3: Select the type of pane you want to freeze. Step 3a: To freeze the top row, select Freeze Top Row. Step 3b: To freeze the first column, select Freeze First Column. … continued 146 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 146 2019/09/20 11:54 AM

Guided activity 10.7 … continued Step 3c: To freeze a selection of rows: Select the row directly below the one you want to freeze. Select Freeze Panes. To freeze a selection of columns: Select the column directly after the one you want to freeze. Select Freeze Panes. PROTECTING THE WORKBOOK If you are going to be sharing the Excel workbooks with other users, you may want to stop those users from making changes. Protecting the workbook means preventing others from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets and renaming worksheets by setting up a password. You can also lock specific cells in a spreadsheet. Guided activity 10.8 Step 1: Select the Review tab. Step 2: Select Protect Workbook. … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.3 Manipulating worksheets 147 Gr11_CAT_Practical_LB.indb 147 2019/09/20 11:54 AM

Guided activity 10.8 … continued Step 3: Enter a password in the Password box. Step 4: Tick the boxes to allow users to do certain things. If you want to make the spreadsheet read-only, untick all the boxes. Step 5: Select OK. Retype the password and select OK again. Guided activity 10.9 Step 1: Open the file GA 10_9.xlsx. Step 2: Select the cells you want to protect. Step 3: Select the arrow next to Alignment on the Home tab to open the Format Cells dialogue box. … continued 148 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 148 2019/09/20 11:54 AM

Guided activity 10.9 … continued Step 4: In the Protection tab, tick the Locked check box and then select OK. Step 5: Protect the worksheet or workbook. PRINT OPTIONS If you need to print an Excel spreadsheet, there are various options you can choose from. For example, you can choose to print the entire workbook, just a single sheet or a selection. Table 10.1 explains the different print options. Table 10.1: Excel print options PRINT OPTION DESCRIPTION Print Active Sheets Print only the sheet(s) that you have selected to print. Print Entire Workbook Prints all the sheets containing data in a workbook. Print Selection Prints only the cells, rows or columns you have selected. You can also manipulate the layout of a worksheet to make the data easier to read when printed and to save paper. For example, if you have a lot of data on a single worksheet, you can choose how the data will be printed to use fewer pages by: ●● Changing the orientation. Landscape orientation, which means the page has the long edge on the top and bottom, can fit more data on a line. ●● Changing how many columns are on a printed page. You can do this using the scaling options in the Print menu. Figure 10.1: Excel scaling options term 3 I Chapter 10 Working with spreadsheets I Unit 10.3 Manipulating worksheets 149 Gr11_CAT_Practical_LB.indb 149 2019/09/20 11:54 AM

●● Setting the width of the margins. Narrower margins mean that there is more space in PRINTING IN EXCEL the main body of the page for the data to fit on to. ●● Use Scaling, fit to one page in Page Setup. ●● Drag the page break lines as you please in the Page Break View. You can find out more about all the printing options in Excel on Microsoft’s support site. Activity 10.2 https://support.office.com/ Open Act10.2(Rivers).xlsx en-us/article/printing-in- 1. Work in Rivers sheet. excel-9785e791-de6f-48dd- a. Protect columns A-C to stop users from entering text. 9b0d-899d75c33d69 b. Use 1234 as a password. 2. Work in the Rivers sheet. a. Change the page layout to ensure that all the data will fit on one page without adjusting the font size and column widths. 3. Save and close the workbook. 150 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 150 2019/09/20 11:54 AM

UNIT 10.4 Integration techniques Excel is ideally suited to be used with other programs since you can use Excel to create graphs or charts from the data to enhance the Word or PowerPoint documents or to manipulate and check the data before you upload it to an Access database. Excel is also able to integrate with itself, since you can link cells and formulas to other spreadsheets in the same workbook and have the data in one spreadsheet influence the data in another. In this unit, the different Excel integration techniques will be described as well as how Excel can be used for problem solving. LINKING CELLS AND FORMULAS BETWEEN SHEETS Very often, you will want to draw conclusions from a large set of data that is in the same workbook. Instead of manually calculating the data you have, you can link the cells across worksheets so that each worksheet’s data can affect the data in other sheets. This may sound complicated but look at the following guided activity to see how you can link cells and why you would need to. Guided activity 10.10 Step 1: Select the cell you want to add the function to. Step 2: Start by typing “=” and then the function’s name in the formula bar, in this case we’re using AVERAGE. Step 3: Select the tab of the worksheet you want to reference. Step 4: Select the range of cells or the single cell you want to reference. Note that the sheet name is followed by an exclamation mark (!). … continued term 3 I Chapter 10 Working with spreadsheets I Unit 10.4 Integration techniques 151 Gr11_CAT_Practical_LB.indb 151 2019/09/20 11:54 AM

Guided activity 10.10 … continued Step 5: Close the bracket and press Enter. Go back to the first sheet to see the result. You can type the information in Steps 3 and 4 manually if you know the exact range of cells or cell you want to reference. Activity 10.3 A local Pokémon GO! community group has been hosting their special events at a nearby zoo. They sell tickets for R45. They have captured data for each of the events they sold online tickets for. They want to see how many tickets they have sold on average since the first event, how much money they made at each event and which event had the highest number of ticket sales. Open the spreadsheet Act10.3(Sales data).xlsx and calculate the following: 1. In column C of each sheet (Chikorita Day, Beldum Day and Cyndaquil Day) calculate how much each person paid for their tickets for each event. 2. Calculate the total number of tickets sold and the total sales for each event. This will be done in: ●● Cells B59 and C59 respectively for Chikorita Day ●● Cells B74 and C74 respectively for Beldum Day ●● Cells B39 and C39 respectively for Cyndaquil Day 3. On the sheet called Ticket totals, calculate for each event ●● the number of tickets sold (B2:B4) ●● the average number of tickets sold (C2:C4) ●● The average sales (D2:D4); by linking the calculations for each event to their respective cells on the sheet Ticket totals. REVISION ACTIVITY Open the Chapter 10_Revision Activity spreadsheet. The details of the blood donors at the school are stored in this spreadsheet. Work in the DonorsInfo worksheet. 1. Format the headings in A1:G1 with a red fill and the font colour to be white. (2) 2. Change the format of the heading in cell I2 to resemble the image below: (2) 3. Change the font of rows 1 & 2 to Comic Sans MS. (1) 4. Apply a suitable formatting in column H so that all cells with donors who weights below 60KG are displayed with a light blue fill. (3) 5. Use a suitable formatting in row 1 and 2 to lock the headings so that they are kept visible when you scroll through the rest of the worksheet. (2) … continued 152 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 152 2019/09/20 11:54 AM

REVISION ACTIVITY … continued 6. Learners that donate more than 30 litres of blood are seen as “Peer Promoters” and those less than 30 litres seen as “Beginners”. The words ‘“Peer Promoter” and “Beginner” should appear in column F based on the litres of blood donated in column I. In cell F26, use a function to determine category for Liyema Frans. (3) Work in the Save-a-ton worksheet. 7. General statistics are normally needed of the learners that are taking part in the Save-a-Ton. a. In cell I5, use a function to calculate the number of girls that took part. (2) b. In cell I6 insert a function to determine the number of male adults. (2) 8. Use the totals for the Male and Female in J1:K2 to create a graph similar to the one below. NOTE: The graph must be a column chart. ●● ●● Display a centred overlay chart title: “We support Blood Donation”. ●● Format the column for female to a stack filled with a picture Droplets.jpg. ●● The vertical axis must have a rotated title that reads “Number of Adults”. ●● Legend must be displayed on the left side. ●● Data labels must be displayed inside base. (8) 9. Move the chart/graph to a new worksheet within the existing spreadsheet. Rename the new sheet “Chart”. (2) Work in the Import worksheet. 10. Import the data from the Import.txt file into the Import worksheet. (3) Total: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Import data into a spreadsheet from various sources? 2 Export data from a spreadsheet into various formats? 3 Manipulate spreadsheet data, including moving and copying data, adding headings, freezing panes and viewing gridlines? 4 Protect a spreadsheet using a spreadsheet’s built-in tools? 5 Use different print options, including print area? 6 Link cells and formulas between sheets? term 3 I Chapter 10 Working with spreadsheets I Unit 10.4 Integration techniques 153 Gr11_CAT_Practical_LB.indb 153 2019/09/20 11:54 AM

TERM 3 CHAPTER TEMPLATES AND INTEGRATION 11 CHAPTER OVERVIEW Unit 11.1: Templates Unit 11.2: Integration with other packages Unit 11.3: Problem solving Unit 11.4: Troubleshooting By the end of this chapter, you will be able to: ●● Insert a header or footer. ●● Create templates and save workbooks as templates. ●● Integrate spreadsheet with other packages, such as linking charts and graphs. ●● Plan and design your own documents for specific scenarios or enquiries. ●● Solve problems using spreadsheets. ●● Troubleshoot spreadsheets and find and view Help files in Excel. INTRODUCTION A spreadsheet application’s ability to take large sets of data and perform complex calculations on it has made it one of the most popular spreadsheet programs in the world. This popularity is also due to the fact that Excel makes it easy for users to import data from other sources and to manipulate that data so that it makes sense. RECAPPING HEADERS AND FOOTERS AS WELL AS DATA TYPES You can make a workbook easier to read and more presentable by inserting headers and footers. A header is found in the top margin and the footer is found in the bottom margin. To insert a header or footer: ●● Navigate to the bottom right hand side of the workbook. Select the Page Layout view command. The worksheet will then be shown in the Page Layout view. ●● Select the header or footer that you want to format. In this example we will modify the header at the top of the page. 154 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 154 2019/09/20 11:54 AM

●● The Header & Footer tools tab will appear in the Excel ribbon. From here, you can add page numbers, dates, workbook names and so on. The header will change and then automatically add page numbers. You may also need to format or update the data type of a particular cell (for example, dates, currency and text). To do this, you will: ●● Select the cell or cells that you want to modify. ●● Select the drop-down arrow in the Numbers group on the Home tab. ●● A dialogue box will open; from there you can select the data type that you want. ●● Lastly, select OK and the data type will be applied to the selected cells. Figure 11.1: Recapping headers and footers term 3 I Chapter 11 Templates and integration 155 Gr11_CAT_Practical_LB.indb 155 2019/09/20 11:54 AM

UNIT 11.1 Templates It is easy to create a template from a workbook you use often (like a stocktake sheet or a products list with specific formulas). When you have created a template remember that the template is always there to use over and over again, the spreadsheet you save after you have added the data to the template, is saved with a new file name and can be saved in a different location. Guided Activity 11.1 If you are saving a workbook to a template for the first time you need to start by setting the default personal templates location: Step 1: Select File and then select Options. Step 2: Select Save. Under Save workbooks, enter the path to the personal templates’ location in the Default personal templates location box. This path is typically: C:\Users\[UserName]\Documents\Custom Office Templates. Step 3: Select OK. … continued 156 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 156 2019/09/20 11:54 AM

Guided Activity 11.1 … continued Once this option is set, all custom templates you save to the My Templates folder automatically appear under Personal on the New page (File > New). To save as a Template: Step 1: Open the workbook you want to use as a template. Step 2: Select File and then select Export. Step 3: Under Export, select Change File Type. Step 4: In the Workbook File Types box, double-click Template. Step 5: In the File name box, type the name you want to use for the template. Step 6: Select Save, and then close the template. term 3 I Chapter 11 Templates and integration I 11.1 Templates 157 Gr11_CAT_Practical_LB.indb 157 2019/09/20 11:54 AM

With the template you created above, you can create a new workbook. To use the template, simply follow the instructions in Example 5.11, but instead of searching for the template in Step 2, select Personal to view your own template. Activity 11.1 1. Open a blank Excel document. 2. Add the following headings to cells A1-E1: a. Item b. Units c. Unit price d. Price e. VAT 3. Add the following formula to cell D2: =C2*B2. The answer will be 0. 4. Add the following formula to cell E2: =C2*1.15. The answer will be 0. 5. Save the document as a template called Stock-on-hand.xltx. 158 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 158 2019/09/20 11:54 AM

UNIT 11.2 Integration with other packages Excel integrates very well with other Microsoft Office products like Word and Access. In Word, you can link charts and graphs from Excel and update them in Word when you update them in Excel. You can also import Excel spreadsheets into a Word document. To import charts and graphs in to Word, you have to link the object in the Word document. This creates a link between the two, so any changes you make in Excel will reflect in Word and you can edit the data in Word as a normal Word table and have the data update in Excel. The easiest way to insert an Excel chart into Word is to copy and paste it. Guided Activity 11.2 Step 1: In Excel, select the chart. Step 2: Press Ctrl+C or select the Home tab and then select Copy. Step 3: In a Word document, select where you want the chart to appear. Step 4: Select Paste Special on the Home ribbon. Step 5: Select Paste Link and also select Microsoft Excel Chart Object. The chart will still be linked to the data in the Excel spreadsheet, so when you update the data in Excel, the chart will update in Word. Activity 11.2 Open the workbook Act11.2(Pie chart).xlsx 1. Create a pie chart using the data in Sheet 1. 2. Insert the chart into the Word document, Act11.2(Pie chart).docx. Find the text [insert chart here]. (Remove the placeholder after the chart has been inserted.) 3. The chart should update automatically when the original data is changed. 4. Save and close both files. term 3 I Chapter 11 Templates and integration I Unit 11.2 Integration with other packages 159 Gr11_CAT_Practical_LB.indb 159 2019/09/20 11:54 AM

UNIT 11.3 Problem solving When you use Excel to solve problems, you need to start with an end-goal in mind. Ask yourself what you want the data to show and how you will show it. Not all problems can be solved with the same formulas or functions. Excel is a useful tool to use to solve real life problems. However, it can seem very overwhelming when you are looking at the problem and trying to figure out how to solve it using formulas and functions. At the end of this section, you will see that you do have the skills you need to get there. All you need to solve problems is some planning, some basic Maths skills and an idea of how formulas and functions work. Example 6.13 will show you how you can go about solving a real-life problem with Excel. Solving real-life problems with Excel Julia wants to buy a car for herself after school before she goes to university. She knows she needs to save up but has no idea how much she must set aside each month. At the beginning of her Grade 11 year, her parents tell her that they will give her 50c for every R1 she saves towards her car. She does some research and finds that the car she wants will cost her R150 000. She now needs to figure out how much money she will need to save (minus the money her parents will give her) in order to buy the car at the end of her Matric year. To do this, she needs to break down each step so that she can create the correct formulas to help her. Step 1: First, she needs to work out how much she’ll need to save each month over the next 24 months. She enters the cost of the car in cell A2. Step 2: She then uses the formula =A2/24 in cell B2 to work out how much money she potentially needs to save. This gives her R6 250 Step 3: She knows she can only save R3 000 a month, so she needs to work out how much her parents will be willing to give her based on her savings. She puts the amount in cell C2 and then uses the formula =C2*0.5 in cell D2. Her parents will give her R1 500 a month. She knows that adding those two values together gives her R4 500. This is not enough to buy the car in two years. Julia now needs to rethink how long she has to save for. As you can see by the example, Julia was able to work out that she would need to save for a bit longer to buy her car using simple Excel formulas. 160 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 160 2019/09/20 11:54 AM

UNIT 11.4 Troubleshooting Sometimes, despite how careful you have been in entering data and putting formulas together, something may go wrong with the Excel spreadsheet. Often, it’s something easy to fix, maybe you entered the wrong value somewhere so now the sum is not correct, and other times it may be quite difficult to figure out what exactly went wrong. In this unit, you will learn how to troubleshoot basic Excel issues. You will also learn about Excel’s help files and where to find them. TROUBLESHOOTING SPREADSHEETS Troubleshooting errors with formulas can be a big job, and Excel does give you tools to help you work through those errors (found in the Formula Auditing group in the Formulas tab), but you might not need to use such an advanced tool. Sometimes, all you will need is some knowledge of the problems you are looking at. The error indicators you learned about earlier in this chapter and in Grade 10 are a valuable clue to help you spot what went wrong. If Excel cannot calculate a formula, it will give you an error specific to the problem. You should learn these errors, what they mean and how to correct them. This is often enough to make sure the spreadsheet works as it is supposed to. If Excel gives you an error you are not familiar with or if something less obvious is wrong, you may need to try a different troubleshooting option. Table 11.1 gives you a list of troubleshooting tips. Table 11.1: Troubleshooting tips ERROR DESCRIPTION SOLUTION Wrong result The formula has returned an Highlight the cell and use F9. Excel will evaluate incorrect result according to the expression and give you a result in the the parameters you set. formula bar. This allows you to see any errors in the logic of the expression. Formula The data is complex but the Check that the formula does not contain multiple looks too formula in the Formula bar lines. Check that the right end of the Formula simple looks too simple. does not have an up/down arrow showing. Use the down arrow or Expand Formula Bar button to see the formula. Incorrect The formula returned a date. The formula is in a cell that is formatted as Date value instead of a number. or Time. Change the cell formatting to Number or General to view the correct result. Function The formula you entered Make sure the cell is not formatted as text and returns the displays as the formula or that there is nothing before the equals sign. Make function function and not as a value. sure you have used an equal sign at the start of the expression. term 3 I Chapter 11 Templates and integration I Unit 11.4 Troubleshooting 161 Gr11_CAT_Practical_LB.indb 161 2019/09/20 11:55 AM

EXCEL HELP FILES Sometimes, even Excel experts need help. The Help function in Excel is a useful tool for finding out how to do something or for looking up the answer to a question you have. Just as in Word, there are three ways to access the help files in Excel: 1. Press F1 on the keyboard to open the Help pane. 2. Type the query into the box labelled Tell me what you want to do at the end of the tabs at the top of the document. 3. Select File and then the ? in the top right corner. Activity 11.3 1. Open the Act11.3(Excel_Help).xlsx file and use the Excel Help feature to answer the questions/fix the errors in the functions. 1.1 The question deals with the POWER function. Enter the correct function in cell E3. 1.2 The question deals with the SMALL function. Enter the correct function in cell E6. 1.3 The question deals with the ROUND function. Enter the correct function in cell E16. 1.4 The question deals with the #VALUE error. Enter the correct function in cell E19. 1.5 The question deals with the IF function. Enter the correct function in cell E25. REVISION ACTIVITY Open the Chapter 11_Revision Activity spreadsheet and complete the tasks below. 1. Format row 1 as follows: ●● Apply text wrapping to the headings in row 1. ●● Increase the font size of the text to 14. ●● Apply any gradient fill to cells A1:H1. ●● Freeze row 1 to make navigation easier of the worksheet. (4) 2. In cells B16-H16, use a function to track marks over the school year for each of the subjects. (7) 3. In cells B17-H17, use a function to work out the average over the school year for each of the subjects. Round the average to one decimal place using the Round function. (7) 4. Use conditional formatting to keep track of when the average for Life Orientation went up or down and when the mark was below or above the average. If the mark is below average the cell must appear with a light red fill and if the mark is above average the cell must appear with a yellow fill. (3) 5. Insert a page header as follows: ●● Left section: Page Number ●● Center section: Add your name and surname (3) 6. You need to represent the averages of all the marks for each of the subjects visually. ●● Create a pie chart with the averages that you have determined in cells B17:H17. ●● Display the legend below the chart. ●● Add the following chart title above: ‘AVG Year marks’ ●● Data labels must be displayed outside end. ●● Move the chart to be displayed on a new sheet. ●● Rename the sheet of the chart to: Avg Marks. (8) 7. Save a copy of the workbook as a template in the Chapter 11 file. (1) Total: [33] 162 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 162 2019/09/20 11:55 AM

AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Insert a header or footer? 2 Create templates and save workbooks as templates? 3 Integrate Excel with other packages, such as linking charts and graphs? 4 Plan and design your own documents for specific scenarios or enquiries? 5 Solve problems using spreadsheets? 6 Troubleshoot spreadsheets and find and view Help files in Excel? term 3 I Chapter 11 Templates and integration I Unit 11.4 Troubleshooting 163 Gr11_CAT_Practical_LB.indb 163 2019/09/20 11:55 AM

TERM 4 CHAPTER INTRODUCTION TO DATABASES 12 CHAPTER OVERVIEW Unit 12.1: Basics of Access Unit 12.2: Work with different views Unit 12.3: Database structure By the end of this chapter, you will be able to: ●● Discuss the uses of a database. ●● Describe the role of tables, queries, forms and reports. ●● Explain the difference between records and fields. ●● Describe how field names determine the data type in each field. ●● Define the different data types in an Access database. ●● Describe the structure of a database. ●● Create tables and forms in an Access database. ●● Work with design and table views in Access. ●● Format and edit data and tables in a database. INTRODUCTION In the days before computers were as common and cheap as they are today, people would use filing cabinets to store their data. This could be the patient records a doctor kept or the records of every student who attended a particular school. The data in a filing cabinet would usually be a paper form that was either filled in by hand or typed and the data would be sorted into different drawers using a key piece of information, usually alphabetically by last name or using a unique number (such as a file number). 164 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 164 2019/09/20 11:55 AM

Figure 12.1: A filing cabinet with files Once computers became more available to the general public, these filing cabinets were slowly replaced with databases, which stored the same information in a digital format. Databases are all around us, in ways we do not even think about. When you go to an ATM and put in a card, the information on the magnetic strip is stored on a database somewhere and the PIN is the unique key to enable access to the account data. In a restaurant, the point-of-sales system connects to a database that lists all the food items on the menu. At school, your marks and your classmates’ marks are all stored on a database, ready for the term report to be drawn from that data. Databases are incredibly important tools in our Information Age. Every transaction and action done by people and computers on the internet generates data that has to be stored, sorted, organised and analysed. Databases, from the small ones used by your school to the large ones used by companies like Google, are where this data is stored and used to generate meaningful ideas about the way people interact with the digital world. DATABASES In Grade 10, you learned about Excel and how you can use it to sort through and analyse data using spreadsheets, which are mainly used to perform calculations. A spreadsheet program allows you to store, organise and analyse information. Access, a database application, uses tables in a way that you will recognise from your work with Excel, that is, to store and organise data. In this chapter you will learn what databases can be used for, what tables, queries, forms and reports are and why they are essential to databases. You will also learn about records and field and how field names influence data. Data types and the structure of a database will also be explained. You will be shown how to create a table and form in Microsoft Access, how to add and delete records and fields and how to validate and sort data. You will learn about how to use the Datasheet View and the Design View for different tasks in a database and the basics of formatting and editing data. term 4 I Chapter 12 Introduction to databases I 165 Gr11_CAT_Practical_LB.indb 165 2019/09/20 11:55 AM

UNIT 12.1 Basics of Access A database is a collection of a large amount of data, which is organised into files called tables. Tables give you a structured way to access, manage and update data. DATABASE USES Databases are used in a wide range of fields. A doctor might use a database to store patient information, or a school might store the subjects and marks for each learner attending that school. Larger databases can store the information generated by a company’s sales teams all around the country or the browsing habits of an entire city. Retail stores can use databases to store information on their products and link this information generated by the barcode scanners at each till point to track how much of each product it has at any given time. Databases are basically used anywhere that data needs to be stored and accessed easily. TABLES, QUERIES, FORMS AND REPORTS Databases in Access are made up of four objects: 1. tables 2. queries 3. forms 4. reports Together, these objects allow you to enter, store, analyse and compile data however you want. Figure 12.2: The relationship between database objects 166 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 166 2019/09/20 11:55 AM

In a database, all data is stored in tables, which puts tables at the heart of any database. Like in Excel, the tables in Access are organised into vertical columns, called fields, and horizontal rows, called records. Although data is stored in tables, the other three objects (forms, queries TABLES and reports) are the ways you work with the data. Each of these objects interacts with the records stored in the database’s tables. Forms are used for entering, modifying, and viewing records. Forms make entering data easier because they standardise the way data is entered or captured in the database. They are an easy way to guide people into giving you the correct data you need to enter into a database. When you FORMS use a form in Access, the data goes into the table you want it to go into. Database designers can set restrictions on individual form components to ensure all of the data is entered in the correct format. Forms help keep data consistent and organised, which is essential for an accurate and powerful database. Queries are a way of searching for and pulling data from one or more tables. Running a query is like asking a detailed question to the database. When you build a query in Access, you are defining specific search QUERIES conditions to find the exact data you want. Queries are far more powerful than the simple searches you might carry out in a table in Excel. A well-designed query can give information you might not be able to find just by looking through the data in tables. Reports give you the ability to present data. A timetable or a report card are examples of reports. Reports are useful because they allow you to REPORTS present components of a database in a way that is easy to read. You can customise a report’s appearance to make it look attractive. Access offers you the ability to create a report from any table or query. RECORDS, FIELDS AND FIELD NAMES Fields and records make up the data in a table. As mentioned in the previous section, fields are the columns in the table and records are the rows. Each field will contain a single type of data, for example, a field marked Name will contain only names while a field marked Telephone Number would contain only telephone numbers. The type of data inside a field is always determined by the field’s name. Records, on the other hand, contain all the data in a single row of the table, so in a table called Users, a record will contain the entry’s identifier (or ID), a username, full name, email address and password. All the cells in that row make up the whole record. Figure 12.3: Fields and records in a database table term 4 I Chapter 12 Introduction to databases I Unit 12.1 Basics of Access 167 Gr11_CAT_Practical_LB.indb 167 2019/09/20 11:55 AM

DATA TYPES IN ACCESS There are a number of different data types in Access that you will use. These are shown in Table 12.1. Table 12.1: Data types in Access DATA TYPE PURPOSE EXAMPLE Text Used to store up to 255 characters of Usernames are stored as Text in the field text. Since Access 2013, this data type Username. has been called Short Text. Number Used to store numbers that are not a In the field Contact Number, the users’ currency value. If the values in the field telephone numbers are stored. are needed for a calculation, use this data type. Date/Time Used to store dates and times. The date and time a user created a user profile is marked in the Date and Time fields. Currency Used to store monetary values. Currency The Users table has a field marked values are not rounded off when doing Donations. The data type here notes the calculations. amount of money each user donated. AutoNumber An AutoNumber field is used to generate Each record begins with a User ID field. a unique value that serves no other This field is an AutoNumber field that purpose than making each generates a unique user ID each time a record unique. new record is entered. Yes/No This is used to enter a Boolean value in This table records whether a user has the field. signed up for the newsletter. If they have, the field entry is Yes. if they have not, the field entry is No. Memo A Memo field is used to store a block of The database also contains a field for a text that is more than 255 characters short thank you note. The data type used long. Since Access 2013, this data type here is the Long Text. has been called Long Text. Hyperlink Text or combinations of text and A hyperlink can be a UNC path or a URL. numbers stored as text and used as a It can store up to 2048 characters. hyperlink address. OLE Object Used to attach an OLE Object to a Such as a Microsoft Office Excel record. If you want to use OLE features, spreadsheet. you must use the OLE Object data type. Attachment Attach supported files to a record similar Such as images, spreadsheet files, to attaching files to e-mail messages. documents, charts, and other types of supported files. 168 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 168 2019/09/20 11:55 AM

Activity 12.1 Written Activity 1. What can databases be used for? Give two examples. 2. Explain the function of each of the elements in Access (tables, forms, queries and reports). 3. Describe the difference between fields and records in a database. term 4 I Chapter 12 Introduction to databases I Unit 12.1 Basics of Access 169 Gr11_CAT_Practical_LB.indb 169 2019/09/20 11:55 AM

UNIT 12.2 Work with different views Guided Activity 12.1 Step 1: Select View on the Home or Table Tools tab. Step 2: If you are in Datasheet View, selecting View will switch you to Design View. Step 3: If you are in Design View, selecting View will switch you to Datasheet View. When you are creating database tables, you will use Datasheet View and Design View. Each view does different things, but both work together to help you create a well-designed table. DATABASE VIEW The Datasheet View shows only the data in the database. It allows you to enter and edit data in tables, but you cannot change the formatting of the database (other than adjusting the column width). Figure 12.4: The Datasheet View 170 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 170 2019/09/20 11:55 AM

The Design View allows you to create or change the table, form or other database object and allows you to configure the fields (that is, defining the field properties, etc.). Design View also allows you to set primary keys and restrict the values that can be entered into a table. You cannot change the actual data in the Design View. Figure 12.5: The Design View To work effectively, you will need to switch between the different views as you create and maintain the database tables. It is important that you know what these two views do before you begin creating tables. Before you can add records and fields to a table, you will need to use Design View to design the table. Once that is done, you can switch to Datasheet View to add the data. term 4 I Chapter 12 Introduction to databases I Unit 12.2 Work with different views 171 Gr11_CAT_Practical_LB.indb 171 2019/09/20 11:55 AM

UNIT 12.3 Database structure All Access databases begin with creating tables and adding records and fields to those tables. Getting the basics of creating these tables right is incredibly important if you want the database to be accurate and work correctly. In this unit, you will learn how to create tables and forms, enter data into tables, add and delete records and fields, validate the data and how to use filters to sort the data. You will also learn how to work with the Design and Table views, and how to format and edit database tables. CREATE TABLES As mentioned in Unit 12.1, tables are the backbone of any database. There are two ways to add a table in an Access database: 1. Adding a new table to a new database. 2. Adding a new table to an existing database. Guided Activity 12.2 Step 1: Open Microsoft Access. Step 2: In the Backstage view, select Blank database. Step 3: Type a name for the database into the File Name box. Step 4: To save the database in a different location, select the Folder icon. Step 5: Browse to the location you want to save in. Step 6: Select Create. 172 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 172 2019/09/20 11:55 AM

A new table is automatically created for you whenever you create a new blank database, since a database cannot exist without a table. To add a table to a database that already has data in it, you will need to: Guided Activity 12.3 Step 1: Open Access. Step 2: Select File and then select Open. Step 3: If the database is listed under Recent, select it. If not, select Browse and find the database. Step 4: Select Create. Step 5: Select Table. term 4 I Chapter 12 Introduction to databases I Unit 12.3 Database structure 173 Gr11_CAT_Practical_LB.indb 173 2019/09/20 11:55 AM

PRIMARY KEYS A primary key is the unique identifier for something across the whole database. Primary keys can be single or multiple fields (for example, in one database the primary key could be just the user ID while another will use the user ID, name, last name and email address as the primary key). Figure 12.6: Primary key icon FIELD PROPERTIES Some of the most important field properties to note are: This property also exists for the common Text data type. It enables you to set a FIELD SIZE maximum size limit on data entered into the column. For the Text data type, size refers to the length (number of characters and spaces) of the Text data in the column. This property enables you to set the precise manner in which Access displays or FORMAT prints the data that is located in its Tables. As with Field Size, the format available to select depends on the data type of that column. This feature can be useful in data entry situations. Where Format controls how data INPUT is displayed, Input Mask controls how data is entered into a particular field. Input MASK Mask is available for the following data types: Text, Number, Date/Time, and Currency. DECIMAL In number fields, you can set the number of decimal places to the right of the PLACES decimal point that will be recorded. An important database concept, the default value can help save time in the data DEFAULT entry process. The default value is automatically placed in that column every time a VALUE new record is added. Required simply forces a user to enter some value, using the proper data type, in the REQUIRED designated field. A new record will not be added if the Required field is not properly completed. Activity 12.2 1. Create a new database Activity 12.2. a. Create a table Learner Data. Add the following fields. FIELD NAME DATA TYPE LearnerID Autonumber Name Short Text Surname Short Text Grade Short Text Fees Paid Number b. Set a primary key for the LearnerID field. Save and close the database. 174 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 174 2019/09/20 11:55 AM

FORMATTING TABLES Before you can add data to a database, you will need to format the tables. To do this, you will work in the Design View. In this view, you can add new field names, determine the data type and give the description for each field. Guided Activity 12.4 Step 1: Add a new field name. Step 2: Choose the data type from the list. Step 3: Add a description. … continued term 4 I Chapter 12 Introduction to databases I Unit 12.3 Database structure 175 Gr11_CAT_Practical_LB.indb 175 2019/09/20 11:55 AM

Guided Activity 12.4 … continued Step 4: In the General tab add validation rules and text, align the text in the field and set the default value. Something to know The General tab will have different options depending on the data type you choose. ADDING AND DELETING RECORDS AND FIELDS Once the table has been created, you can then move on to adding data to it. You can do this manually. Guided activity 12.5 Step 1: Select Click to Add. Step 2: Select the data type for that field, in this case Short Text. … continued 176 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 176 2019/09/20 11:55 AM

Guided activity 12.5 … continued Step 3: Give the field a name, in this case Gym Name. Step 4: Begin filling in the data. Step 5: Repeat this for every column. New data will be indicated by a * at the start of the row. Every field you add, adds to the records. Once you have filled in one row in a field, the next record will be created below that. Access will also create an ID for each record as you add data to the table. Adding or deleting records is very simple. Select the box next to the record to highlight it and then right-click to delete it. You will get a warning box that pops up asking if you are sure you want to delete the record. You can also do the same for fields by selecting the field name and then right clicking to delete it. Figure 12.7: Deleting a record and field Activity 12.3 Open the Activity 12.3 database and edit the table tblCode as follows: 1. Remove the ID field. 2. Change the data type for the Entry date field to a more suitable data type. 3. Add a new field called Type, using an appropriate data type. … continued term 4 I Chapter 12 Introduction to databases I Unit 12.3 Database structure 177 Gr11_CAT_Practical_LB.indb 177 2019/09/20 11:55 AM

Activity 12.3 … continued 4. Go to the datasheet view and complete the table by adding the following missing data: CODE NAME ENTRY DATE TYPE E Endangered 12/03/2019 Bird SC Specific Concern 05/02/2019 Bird SR Significantly Rare 10/02/2019 Plant T Threatened 18/03/2019 Plant Save and close the database. CREATING A FORM In Access, you can create a form from an existing table, which will name the form fields based on the field names or create a blank form where you define the form fields yourself. Guided activity 12.6 Step 1: Select the Create tab. Step 2: Select Form Wizard. Step 3: Select the table that contains the data for the form in the GAct 12.6 file. … continued 178 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 178 2019/09/20 11:55 AM

Guided activity 12.6 … continued Step 4: Choose the fields you want to appear on the form and select Next. Step 5: Choose the layout for the form. You can choose between columns, tables, datasheet or justified views. Step 6: Give the form a name and then choose whether you want to open the form to view it or if you would like the change the form’s design. Creating a blank form has a slightly longer process. Blank forms have no controls or preformatted elements. Guided activity 12.7 Step 1: Select the Create tab. … continued term 4 I Chapter 12 Introduction to databases I Unit 12.3 Database structure 179 Gr11_CAT_Practical_LB.indb 179 2019/09/20 11:55 AM

Guided activity 12.7 … continued Step 2: Select Blank Form. Step 3: The Design view becomes available, under the Form layout tools. Step 4: Access will open a blank form in the Layout view and displays the Field List pane. Step 5: Select the + sign next to the table or tables that have the fields you want to see in the form in the Field List pane. Step 6: Double click or drag the field onto the form. DATA VALIDATION AND SORTING A data validation rule is one way to restrict what is entered into a table field or on a form. Validation text lets you give users who input the incorrect information or invalid data. There are built in data validation rules in Access: ●● Data types restrict what data can be entered into a field. For example, a Date/Time field will only accept dates and times, a Number field will not accept text, etc. ●● Field properties can also restrict data input. For example, if the amount of data is more than the maximum allowed by the Field Size property, the input will be invalid. To easily find what you are looking for in a database, you can use filters to sort the data, just like in Excel. To use the filters, select the down arrow next to the field name and select how you want the data sorted. You can choose to sort the fields alphabetically, from smallest to largest, etc. You can also choose to filter out all records that do not share the same criteria (like Bot ID in our example). 180 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 180 2019/09/20 11:55 AM

Activity 12.4 Open the Activity 12.4 database. 1. Create a form called frmSpecies based on the Survey table as follows: a. Include all the fields of the Survey table. b. Make sure the form has a tabular layout. c. Change the font of the form heading to bold and change the background of the form to any colour of your choice. Save and close the form. REVISION ACTIVITY WRITTEN ACTIVITY Match the description in Column B to the term/description in Column A. COLUMN A COLUMN B 1 Database A Shows only the data in the database. 2 Table B Searching for and extracting data from tables. 3 Record C Different folders. 4 Field D Allows you to create/ change the table or other database object and allows you to configure the fields. 5 Datasheet View E A collection of a large amount of data. 6 Design View F Used for entering, modifying, and viewing records. 7 Primary Key G Present components of the database in a way that is easy to read. 8 Queries H Contain a single type of data. 9 Form I Contain all the data in a single row of the table. 10 Report J A set of related records/ data. K Unique identifier for something across the whole database. L All the data on the computer. (10) PRACTICAL ACTIVITY Open the Chapter 12_Revision Activity database. 1. Create a new table: Sales. The table must consist of the following fields. Note carefully the data type and the size of each field: FIELD NAME DATA TYPE FIELD SIZE/TYPE Customer ID AutoNumber Primary key Customer name Short text 20 Customer surname Short text 25 Shipping Short text 30 Category Short text 40 Date received Date / Time Medium date (12) … continued term 4 I Chapter 12 Introduction to databases I Unit 12.3 Database structure 181 Gr11_CAT_Practical_LB.indb 181 2019/09/20 11:55 AM

REVISION ACTIVITY … continued 2. Switch to Design View and enter the following records: CUSTOMER CUSTOMER CUSTOMER DATE SHIPPING CATEGORY ID NAME SURNAME RECEIVED 1 Claire Nelson Express Furniture 19-June-19 2 Sean Powers Standard Technology 22-May-19 3 Andrew Smith Express Office 10-May-19 Supplies 4 Sandra Grove Express Furniture 15-April-19 5 Eric Hoffman Standard Technology 20-April-19 (5) Save and close the table. 3. Create a form called frm_Sale records based on the Sales table as follows: a. Include all the fields of the Sales table. b. Make sure the form has a tabular layout. (3) Save the form and close the database. Total: [30] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Discuss the uses of a database? 2 Describe the role of tables, queries, forms and reports? 3 Explain the difference between records and fields? 4 Describe how field names determine the data type in each field? 5 Define the different data types in an Access database? 6 Describe the structure of a database? 7 Create tables and forms in an Access database? 8 Work with design and table views in Access? 9 Format and edit data and tables in a database? 182 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 182 2019/09/20 11:55 AM

TERM 4 CHAPTER TABLES AND QUERIES 13 CHAPTER OVERVIEW Unit 13.1: Design database tables Unit 13.2: Queries By the end of this chapter, you will be able to: ●● Apply custom formatting to fields and records in an Access database. ●● Describe other applications that Access can be integrated with. ●● Choose the correct data types for tables. ●● Set field properties for data fields. ●● Design and execute basic queries. ●● Select which fields to display in a query. ●● Troubleshoot basic Access errors. ●● Describe how Access can be used to solve problems. INTRODUCTION Databases can also be customised to make reading the data easier. In this chapter, you will learn how to apply custom formatting to fields and records in a database and how to integrate Access into other applications. You will also learn how to troubleshoot basic Access errors and describe how you can use Access to solve problems. term 4 I chapter 13 Tables and queries 183 Gr11_CAT_Practical_LB.indb 183 2019/09/20 11:55 AM

UNIT 13.1 Design database tables A properly designed database will always give you accurate and up-to-date information when you need it. This is why it is important that you understand how to design a database correctly and how to make a database that can adapt to your changing needs. In this unit, you will learn about the basics of good database design, how to choose the correct data types for a database and how to set field properties correctly for the fields. Before deciding to build a database, you may want to look at the templates included in Access to see if any of them match your needs. When you select a template, Access creates a new database based on that template. Once it is created, you can fill the database with your own information or modify it to suit your needs. These template databases are well designed and flexible and are built to meet most common database needs. BASICS OF GOOD DATABASE DESIGN There are two main principles that determine good database design: ●● No redundant data ●● Correct and complete information ●● Redundant data is data that is duplicated, meaning that there is more than one record with the same information in it. Redundant data in a database is bad, because it takes up unnecessary space and it can lead to errors when you are creating a report. Information should also be complete and correct because if the data in the database is wrong, any reports you pull from that information and any conclusions you make using those reports will also be wrong. ●● A good database design: ¡¡ Divides information into subject-based tables to limit the amount of redundant data. ¡¡ Gives the database program the information it needs to join the data in the tables together correctly. ¡¡ Helps to make sure the data is accurate and maintains its integrity. ¡¡ Matches data processing and reporting needs. ●● Before you can begin designing database tables, you need to first determine the purpose of the database, that is, what it will be used for. You then need to find and organise the information you will want to record in the database, starting with the information you already have (such as customer details or song title lists). Once you have sorted the information you want, you can design the database tables. For example, say you have a spreadsheet with customer details that contains the following information: 1. Company name 2. Contact name 3. Telephone number 4. Email address 5. Street address, city, province 6. Post code You then break it down into the tables you think it should be in. The easiest way to do this is to use some form of visual representation. This could be a piece of paper that you draw on, a whiteboard or a blank Excel spreadsheet. In this case, we are using an Excel spreadsheet. 184 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 184 2019/09/20 11:55 AM

Guided activity 13.1 Step 1: Sort through the data and break down what you have into columns and rows. Step 2: Map out the tables according to the data you have and give each field a name. Make sure that the ID fields are at the start of each table. Now that you have the basic idea for the database, you can move on to choosing the correct data type for each field. Activity 13.1 1. Why is it important to have properly designed databases? 2. Which two principles determine good database design? 3. What is redundant data and why is it bad for a database? 4. What benefits does a good database design give you? 5. What is the first step in designing a good database? CHOOSING APPROPRIATE DATA TYPES The data types you choose for the tables will be determined by the information you have available and what you want to the database to do. But there are other data types that you will need to use if the ones you learned about previously do not suit your needs. Other data types you could use are: ●● Hyperlinks, which are text or combinations of text and numbers stored as text that are used as a hyperlink address. ●● Lookups, which display a list of values taken from a table or query or a set of values you specified when you created the field. ●● OLE, or object linking and embedding, objects are information created by other programs (including Word and Excel) that can be embedded in and linked to an Access database. You can also use the Attachment data type to do this. term 4 I chapter 13 Tables and queries I Unit 13.1 Design database tables 185 Gr11_CAT_Practical_LB.indb 185 2019/09/20 11:55 AM

Guided activity 13.2 This may seem like a simple step, but it can actually take the longest and you will need to ask yourself some interesting questions and give yourself good answers. In this example, we had to ask: ●● Why is telephone number not a Number field? They all start with zero and so do some postcodes. ●● Why are there two IDs in Addresses and Contacts? Maybe we get more than one contact for each hotel or guesthouse and we need to link them to something. ●● Why are we checking the post code? We are not, we are checking that the address given to us is a postal address. ●● Why is there a create date in the Companies table? We would like to track how long this hotel or guest house has been buying our products. FIELD PROPERTIES When you are choosing data types, you will also need to set the properties for each field. Different data types do have some specific field properties (such as a decimal place for numbers), but there are common data type properties as well. PROPERTY DESCRIPTION Size/length Determines how much space is allowed for each value. This is determined by the data type. If you are using a Short text field, you can limit the amount characters that can be used. This is especially important for Number and Currency fields because it determines the range of the numbers (for example, one bit can only store from 0–255). Default Automatically assigns a specified value to a field when a new record is added (for value example, a user ID when a new user is added). This field property is common to all data types and Access will automatically assign that data type to a field in the record when you add a new record. Decimal Specifies how many decimal places to use when displaying numbers (for example, places two decimal places for Currency or four decimal places for Number). Required Data must be entered into that field (for example, a username for a social media website). This is usually used when creating the unique identifiers, such as user IDs, in a field using AutoNumber. In a database, you could potentially have 45 J. Smiths, but each one will have a different user ID to make their record unique. Input mask A string of characters that indicates the format for valid input values. You use this property when it is important that the format of the input values is the same. For example, Access will not add the value in a telephone number field unless it contains 10 digits. Validation Specify a criterion that all valid field values must meet before they are added to the rule database. For example, in a currency field, setting the field to not accept data that is less than or equal to zero (>=0). Validation An error message that displays describing what went wrong when a data entry text breaks a validation rule, for example, “Price cannot be a negative number”. Text align Specifies the default alignment of text in a field. You can set this to left, right or centre. 186 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 186 2019/09/20 11:55 AM

ADDING INPUT MASKS, VALIDATION RULES AND VALIDATION TEXT Input masks and validation rules and text are slightly more complicated to add to a field than other field properties. This section will show you how to add them to fields in a database. There are three parts to an input mask: 1. The first part contains the mask characters or series of characters (the string) along with placeholders and literal data, like brackets, full stops or hyphens. This part must be included. 2. The second part is the embedded mask characters and how they are stored in the data. If this is set to 0, the characters are stored in the data, if it is set to 1, the characters are only displayed. This part is optional. 3. The third part indicates a single character or space that is used as a placeholder. This is also optional. term 4 I chapter 13 Tables and queries I Unit 13.1 Design database tables 187 Gr11_CAT_Practical_LB.indb 187 2019/09/20 11:55 AM

Guided activity 13.3 Step 1: Right click on the table in the Navigation pane on the right side of the screen. Step 2: Select Design View. Step 3: Select the field you want to add the input mask to. Step 4: Select the Input Mask box in the Field Properties Group on the General tab. Step 5: Select the Build button to start the Input Mask Wizard. … continued 188 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 188 2019/09/20 11:55 AM

Guided activity 13.3 … continued Step 6: In the Input Mask list, select the type of mask that you want to add. Step 7: Select Try It and enter data to test how the mask displays. … continued term 4 I chapter 13 Tables and queries I Unit 13.1 Design database tables 189 Gr11_CAT_Practical_LB.indb 189 2019/09/20 11:55 AM

Guided activity 13.3 … continued Step 8: To keep the input mask without any changes, select Next. Otherwise, select Edit List and make the changes you want. CHARACTERS THAT DEFINE INPUT MASKS https://support.office.com/ Select Close when you are done. en-us/article/control-data- Step 8a: Select Try It and then select Next. entry-formats-with-input- masks-e125997a-7791- 49e5-8672- Step 9: Select an option for how you want the data to be stored. 4a47832de8da#characters Step 10: Select Finish and save the changes. 190 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 190 2019/09/20 11:55 AM

VALIDATION RULES AND TEXT There are two types of validation rules: 1. Field Validations Rules specify the criteria that all valid field values must meet, for example, a date field may have a restriction that does not allow dates in the past. 2. Record Validation Rules specify the condition that valid records must meet. For example, a record with two date fields might specify that the first date field must always be a date before the second date field (that is, the start date must be before the end date). VALIDATION RULE EXAMPLES TEXT VALIDATION RULE EXAMPLES EXAMPLE FUNCTION Like “G*” Word must start with G “M” or “F” Only enter ‘M’ (Male) or ‘F’ (Female) Like “*@*” Enter a valid email address Is Not Null Field cannot be left empty NUMBER VALIDATION RULE EXAMPLES EXAMPLE FUNCTION >0 Only positive numbers / numbers greater than 0 >=5 Only numbers greater than or equal to 5 >=10 And <= 100 Numbers between 10 and 100, including 10 and 100 Between 10 And 100 Numbers between 10 and 100, including 10 and 100 2 Or 3 Or 4 Only 2, 3 or 4 DATE VALIDATION RULE EXAMPLES Dates should be written according to the South African Regional Settings (yyyy/mm/dd) EXAMPLE FUNCTION >#2019/02/28# Only dates after 28 February 2019 Between #2019/03/01# And #2019/03/31# Only dates in March 2019 >Date() Dates after today’s date term 4 I chapter 13 Tables and queries I Unit 13.1 Design database tables 191 Gr11_CAT_Practical_LB.indb 191 2019/09/20 11:55 AM

Guided activity 13.4 To create field validation rules: Step 1: Select the field that you want to validate. Step 2: Select Validation Rule in the General tab. And then select the Build button to start the Expression Builder. Step 2a: Enter an expression into the top field. In this case, we do not want users to enter a date earlier than 21/02/2019. After or later than, will be indicated by “>” and the “=” suggest that the date 21 Feb 2019 is included. So, dates on, or after the 21st will be accepted in this example. Step 2b: Select OK. Step 3: To add validation text, select the field that needs a message. The field should already have a validation rule. … continued 192 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 192 2019/09/20 11:55 AM

Guided activity 13.4 … continued Step 4: Select Validation Text in the General tab. Step 5: Enter the message you want to display. The message will appear as a pop-up if data is typed in the field which is not accepted according to the rule. To create record validation rules: Step 1: Open the table you want to validate records for. Step 2: Select Validation, and then select Record Validation Rule. This is on the Fields tab, in the Field Validation group. Step 3: Use the Expression Builder to create the rule. Step 4: To add validation text, open the table you want to add a message to. The table should already have a record validation rule. Step 5: Select Validation and then on Record Validation Message. This is in the Field Validation group on the Fields tab. Step 6: Enter the message you want to display. For example, if the validation rule is [StartDate]<[EndDate], the message might be “StartDate must precede EndDate”. Below are error messages that you may get when you enter invalid information: term 4 I chapter 13 Tables and queries I Unit 13.1 Design database tables 193 Gr11_CAT_Practical_LB.indb 193 2019/09/20 11:55 AM

UNIT 13.2 Queries At their most basic, queries are the language you use to display data from a database. It is the way you interact with the database. Using queries makes it easier to view, add, delete or change data, it also allows you to find specific data quickly by filtering using specific conditions, calculate or summarise data and make some data management tasks, like reviewing the most current data, automated. In a well-designed database, the data is usually located in multiple tables. Working through those tables on your own to find the information you need to create a form or report can be time-consuming and can lead to errors. Queries can pull the information from the various tables, assemble it and display it for you far faster than you would be able to. There are two major query types: 1. Select queries that fetch data from tables or do calculations. 2. Action queries that add, change or delete data. In this unit, you will learn how to design basic queries using the example of a simple select query. As part of this process, you will also learn how to select the fields you want to display in a query. DESIGN BASIC QUERIES (AND, OR, NOT, SORT) Once you know how to create a query in Access, you will be able to work through the database at lightning speed. When you want to select specific data from one or more sources, you will use a select query. This query helps you get only the data you want and helps you combine data from several sources. In this section, you will learn how to create a select query manually in Access using the Design View. This is a very long process and it is easy to make mistakes if you are not careful. You will need to take your time and make sure that you understand each step in the process and that you have done each step correctly before moving on to the next step. Guided activity 13.5 Option 1: Take note Step 1: Add data sources. Step 1a: Select Query Design in the Queries group on the Create tab. There are two different options shown for Steps 1 to 3 in this guided activity. Both options then continue with same steps from Step 4 onwards. … continued 194 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 194 2019/09/20 11:55 AM

Guided activity 13.5 … continued Step 1b: In the Show Table dialogue box double-click each data source that you want to use or select each data source and then select Add. Step 1c: Close the Show Table dialogue box. Step 2: Add output fields. Step 2a: In the Query Design Window, select the field you want to add. When you add a field this way, Access automatically fills in the Table row of the design grid to show the data source. Step 3: Specify criteria. This step is crucial; a query can only exist if you have criteria. No criteria and the full table will be displayed. Step 3a: Type the criteria in the Criteria row in the query design grid. This will be the data that has been typed in the table and should be typed in the same format. The question might ask display all the General Managers, but in the criteria, you will only type General Manager (singular, as this is the way the data has been typed). … continued term 4 I chapter 13 Tables and queries I Unit 13.2 Queries 195 Gr11_CAT_Practical_LB.indb 195 2019/09/20 11:55 AM

Guided activity 13.5 … continued Step 3b: To find matches to multiple criteria, use the Or: field in the query design grid. This will return records where one or the other is found. Step 3c: To exclude a field, use the word “Not” in the Criteria row. Step 3d: To find more than one specific record, use the word “And” between two criteria in the Criteria row, for example “General Manager” And “Hospitality Manager”. Option 2: Step 1: Add data sources. Step 1a: Select Query Wizard in the Queries group on the Create tab. Step 1b: Select Simple Query Wizard. … continued 196 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 196 2019/09/20 11:55 AM

Guided activity 13.5 … continued Step 2: Add output fields from Available Fields on the Query Wizard and select Next. Select Detail Query and Next. Step 3: Select Finish on the Query Wizard Dialogue box and view your Query. To specify criteria, you select View Design. Step 4: Summarise the data if asked to do so. Step 4a: With the query open in Design view, select Totals in the Show/Hide group on the Design tab. Access displays the Total row in the query design grid. Step 4b: For each field that you want to summarise, choose the function you want to use from the list in the Total row. The functions that are available depend on the data type of the field. Step 5: View the results. Step 5a: Select Run on the Design tab to see the query results. Access displays the results of a query in the Datasheet View. Step 5b: To make further changes to the query, select the Home tab. Step 5c: Select View and then Design View to switch back from the Datasheet View. Step 5d: If necessary, change fields, expressions or criteria, and rerun the query until it returns the data that you want. Activity 13.2 Open the Activity 13.2 database. 1. Create a query called qrySchool based on the Contestant table. a. Extract all learners from Cape High School and Bay High School who are 18 years old. b. Display only the Surname_Van, Name_Naam, School_Skool and Age_Ouderdom fields. Save and close the query. term 4 I chapter 13 Tables and queries I Unit 13.2 Queries 197 Gr11_CAT_Practical_LB.indb 197 2019/09/20 11:55 AM

REVISION ACTIVITY A database was created to capture data about different species and survey data about their endangered status. Open the Chapter 13_Revision Activity database. 1. Work in the tblSurvey table and make the following changes to the design of the table: a. Change the field size of the CName field to 40. (2) b. The Status field must be a required field. (2) c. Set the ID field as the primary key. (2) d. Create an input mask for the Status field to accept data in the following format: ●● a compulsory capital letter followed by ●● an optional capital letter. ●● Examples: SR, T, etc (4) e. Set a validation rule to ensure that users are not allowed to capture dates before 2012 for the SurveyDate field. If they should enter invalid dates the following validation text must appear: ‘No entries before 2012’. (4) f. Change the row height of the table to 20 units. (1) g. Change the font size of the table to 12pt and the font type to Georgia. (2) h. Set all the gridlines on red. (1) 2. Create a query called qryPlant based on the Survey table. ●● Display only the Type, CName and Protected fields. ●● Sort the data in ascending order according to the CName Field. (4) 3. Create a query called qryPopulation based on all the fields of the Survey table. ●● Do not show the Habitat and ID fields. ●● Only species of a population of more than 500 must be displayed. ●● Show all the species with a “T” or ‘SR” status. (4) Total: [26] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Apply custom formatting to fields and records in an Access database? 2 Describe other applications that Access can be integrated with? 3 Choose the correct data types for tables? 4 Set field properties for data fields? 5 Design and execute basic queries? 6 Select which fields to display in a query? 7 Troubleshoot basic Access errors? 8 Describe how Access can be used to solve problems? 198 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 198 2019/09/20 11:55 AM

TERM 4 CHAPTER REPORTS 14 CHAPTER OVERVIEW Unit 14.1: Reports Unit 14.2: Formatting technique By the end of this chapter, you will be able to: ●● Design basic reports that include calculations and headers and footers. ●● Import and export data to and from an Access database. INTRODUCTION In this unit, you will learn how to create basic reports and have basic calculations, such as SUM, AVG, COUNT, MIN and MAX at the end of the report. You will also learn how to add headers and footers to reports and how to import data into and export data from an Access database. term 4 I chapter 14 Reports 199 Gr11_CAT_Practical_LB.indb 199 2019/09/20 11:55 AM

UNIT 14.1 Reports Reports are one of the most useful tasks that a database can do. Reports give you a way to format, view and summarise the information in an Access database for a huge range of different applications. For example, you can create a simple report that gives you the contact information of all the students in your Grade or you can extract a more complex report that lists a company’s total sales across all their branches for the last three years. DESIGN BASIC REPORTS A report is a database object that you can use to present information for any of the following reasons: ●● To display or distribute a summary of the data ●● Archive snapshots of the data ●● Give details about individual records ●● Create labels Creating a report in Access is relatively easy, as long as the database is well-designed. DIFFERENT SECTIONS OF A REPORT a. Report Header: At the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. b. Page Header: At the top of every page. Use a page header to repeat the report title on every page. c. Group Header: At the beginning of each new group of records. se the group header to print the group name. d. Detail section: Appears once for every row in the record source. This is where you place the controls that make up the main body of the report. e. Page Footer: At the end of every page. Use a page footer to print page numbers or per-page information. f. Report Footer: At the end of the report. Use the report footer to print report totals or other summary information for the entire report. Figure 14.1: The different sections of a report 200 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 200 2019/09/20 11:55 AM

Guided activity 14.1 Step 1: Open under the Create tab, locate the Report group and select the Report Wizard. Step 2: Select the table or query on which the report should be based. Step 3: Select the fields you want on the report: ●● will add one field at a time ●● will add all the fields in one click Select Next. Step 4: Choose how you want the records to be sorted. … continued term 4 I chapter 14 Reports I Unit 14.1 Reports 201 Gr11_CAT_Practical_LB.indb 201 2019/09/20 11:55 AM

Guided activity 14.1 … continued Step 5: Choose how you want to lay out the report. Step 6: Give the report a title. EXAM TIP Create the habit of changing the radio button to Modify the report design, as this will always be asked in examinations. Step 7: Select Finish. 'The report will then open in the design view, where you can continue with formatting and editing Once you have finished creating the report, you can format it in the Layout view. Activity 14.1 Open the Activity 14.1 database. 1. Create a report called Country based on the Info_Inlig table as follows: a. Display only the following fields: Surname_Van, Country_Land, Departure_Vertrek and Arrival_aankoms. b. Sort the records in the report according to the Surname_Van field. c. Use the outline layout. Save and close the report. 202 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 202 2019/09/20 11:55 AM

BASIC CALCULATIONS IN A REPORT You can add basic calculations to a report in either the Layout view or the Design view. Table 14.1 gives a breakdown of the basic calculations which can be added to a report. Table 14.1: Basic report calculations CALCULATION DESCRIPTION FUNCTION Sum The sum of all the numbers in the column. Sum() Average The average value of all the numbers in the column. Avg() Count The count of items in the column. Count() Maximum The highest numeric or alphabetic value in the column. Max() Minimum The lowest numeric or alphabetic value in the column. Min() Guided activity 14.2 Step 1: Right click the report in the Navigation pane and then select Design view. Step 2: Select the field you want to summarise. Step 3: Select Totals in the Grouping & Totals group in the Design tab. Step 4: Select the calculation you want to add for that field. term 4 I chapter 14 Reports I Unit 14.1 Reports 203 Gr11_CAT_Practical_LB.indb 203 2019/09/20 11:55 AM

Guided activity 14.3 Step 1: Right click the report in the Navigation pane and then select Design view. Step 2: Select the field you want to summarise. Step 3: Select Totals in the Grouping & Totals group in the Design tab. Step 4: Select the calculation you want to add for that field. HEADERS AND FOOTERS Headers and footers are report sections you can use to display information that is common to the entire report, or to each page of a report. For example, you can add a Page Footer section to display a page number at the bottom of each page, or you can add a Report Header section to display a title for the entire report. Guided activity 14.4 Step 1: In the Navigation Pane, right click the report that you want to change, and then select Design View on the shortcut menu. Step 2: Verify which sections are already on the report. The sections are separated by shaded horizontal bars called section selectors. The label on each section selector indicates what the section directly below it is. Every report has a Detail section and can also contain Report Header, Page Header, Page Footer and Report Footer sections. In addition, if there are grouping levels in the report, you might see group headers or footers. By default, group headers and footers are named by using the field name or expression that is the basis of the group. To have access to the group footers and headers, you need to open the group and sort command on the ribbon. … continued 204 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 204 2019/09/20 11:55 AM

Guided activity 14.4 … continued Step 3: To add page header and footer sections or report header and footer sections to a report, right click any section selector and then select Page Header/Footer or Report Header/Footer on the shortcut menu. EXAM TIP When you have completed the Wizard to create a report, always select Modify the report design. Do the following before you continue with the questions: Open the area for the report footer, by dragging the double arrow cursor. Calculations based on the entire report will always be done in the report footer. Select Group & Sort and check that you have grouped and sorted correctly. Select More and change the drop-down arrow to with a footer. Any calculation based on the group, must be completed in the group footer. Page footers will only contain dates, times and page numbers. term 4 I chapter 14 Reports I Unit 14.1 Reports 205 Gr11_CAT_Practical_LB.indb 205 2019/09/20 11:55 AM

Activity 14.2 Open the Activity 14.2 database. 1. Create a report called rptFees based on the tblBookings table. a. Display only the Name, UnitType, Days and AmountReceived fields. b. Use a function to display the total amount received by unit type. c. Use a function in the Report Footer to display the average amount of all the money received. The amount must be displayed as currency (SA Rand). d. Use the tabular layout for the report. Save and close the database. IMPORT/EXPORT DATA You can import data from an Excel spreadsheet to an Access database, but it is far easier to copy the information from Excel and paste it in Access. Guided activity 14.5 Set up Access to link to data Step 1: Select the column you want to copy and then press Ctrl+C. Step 2: Open a new Notepad document and paste the data there by pressing Ctrl+V. For more information on Step 3: Copy the data from Notepad. how to import a whole Step 4: In Access, select the field you want to populate, making sure to select the whole column spreadsheet at once, visit: (select the field name at the top) and then press Ctrl+V. https://support.office.com/ The data should paste in the field. en-us/article/import-or-link- to-data-in-an-excel- workbook-a1952878- You can also import data into Access from a text file, such as a *.csv file. You will need to 7c58-47b1-893d- prepare the text file properly before you start the import process. It is easiest to import data e084913cc958 from a delimited CSV file that uses either a comma or semicolon (;) as the delimiter. To prepare the file, open it in Notepad and check that the delimiter is used consistently throughout the file. The file should also not have more than 255 fields and you should remove any extra characters (such as double spaces and tabs). Make sure the field names are in the top row and that they are separated with the delimiter. Do not work with the source file (for example, the Excel file) still open. Once you have prepared the file, you can follow the steps in the example below to import the data. 206 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 206 2019/09/20 11:55 AM

Guided activity 14.6 Step 1: Open the Access database you want to import to or create a blank database. On the External Data tab in the Import&Link group you have to select the application you need to import the data from. Step 2: If you are importing an Excel file, you select Excel and on the dialogue box, you browse and select the file. Step 2a: To add the data to a new table, select Import the source data into a new table in the current database. Step 2b. To add data to an existing table, select Append a copy of the records to the table and choose the table from a drop-down list. Step 2c. A link to the source file can also be created. Step 3: On the Import Spreadsheet Wizard select which worksheet you want to use (if applicable). Step 4: If the first row of your spreadsheet has column headings, then select these as field names for your table (place a tick in the checkbox). Step 5: In this step you can confirm (and change, if need be) the field names and data types of each field. Step 6: In the next step Access can add a primary key (an Autonumber field called ID) OR you can select one of your fields as primary key OR you can create the table without a primary key. Step 7: In this final step you can choose a name for your table. … continued term 4 I chapter 14 Reports I Unit 14.1 Reports 207 Gr11_CAT_Practical_LB.indb 207 2019/09/20 11:55 AM

Guided activity 14.6 … continued Step 8: Open the table in the Datasheet View and compare the data with the source document. Check that everything is correct. Activity 14.3 1. Create a new database Activity 14.3. a. Create a table by importing the data from the Learners spreadsheet. b. Use the worksheet name as the table name. Save and close the database. 208 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 208 2019/09/20 11:55 AM

UNIT 14.2 Formatting techniques You can use the Design View to apply custom formatting to the database. To do this, you enter different characters into the Format property of a table field. These characters are placeholders (like # or 0), separators (like full stops and commas), literal characters and colours. There are different placeholders for different data types. In Guided activity 14.7, we are going to format a text field. Guided activity 14.7 Step 1: In the Navigation Pane, right-click the table that you want to change, and then select Design View on the shortcut menu. Step 2: Select the field that you want to format. Step 3: On the General tab, select the cell next to the Format box. Step 4: Enter the format. In this case, we want the last names to be Magenta. Step 5: Press CTRL+S to save your work. term 4 I chapter 14 Reports I Unit 14.2 Formatting techniques 209 Gr11_CAT_Practical_LB.indb 209 2019/09/20 11:55 AM

UNIT 14.3 Integration As you learnt in Chapter 7, Access can be integrated with Word to provide data during a INTEGRATING ACCESS Mail Merge. Access can also be integrated with: For more information on how to ●● Windows SharePoint Services integrate Access with other ●● Microsoft Outlook programs, visit HYPERLINK ●● Microsoft SQL Server ●● Azure SQL Server Database In most cases you can import data, which creates a copy of the data and does not synchronise any updates, or you can link the data, which creates a two-way connection so that when you make changes in Access, the changes reflect in the other platform. Each program you want to integrate Access with has a different procedure for linking. https://support.office.com/ en-gb/article/import-and- export-c060505b-d8ac-4499- 8879-733e56c6106f?ui=en- US&rs=en-GB&ad= GB#ID0EAABAAA= Import_and_link 210 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 210 2019/09/20 11:55 AM

UNIT 14.4 Troubleshooting Since databases can contain a large amount of data and the links between that data can be complex, things can sometimes go wrong. When this happens, you can very often fix an issue yourself. If something does go wrong, you can try the following: ●● Make sure the data type has been selected correctly for the field. ●● Check to make sure your queries have been formulated correctly. ●● Check the data to make sure that it is complete. ●● Restart Access, making sure to save your work. ●● Backup your data and then copy it all to a blank database. ●● Shut your computer down. ●● Make sure you have the latest version of Access with all of the latest patches and fixes. ●● In extreme cases, you may need to uninstall Access and reinstall it. If this does happen, make sure you have a backup of your data. ●● You can also use the Help pane in Access by pressing F1 on your keyboard and searching for your error or using the Tell me what you want to do search bar next to the tabs in Access. You can also visit the Microsoft support website and search for your problem. term 4 I chapter 14 Reports I Unit 14.4 Troubleshooting 211 Gr11_CAT_Practical_LB.indb 211 2019/09/20 11:55 AM

UNIT 14.5 Problem solving with databases While it is important that you learn how to use Access to create and maintain databases, you also need to know why you need to use Access. Like with Excel, Access can be used to solve real-world problems. Examples of how you can use databases to solve problems are listed below: ●● Help businesses keep track of customer, product and sales data, and link them to each other to draw conclusions about how well certain products are performing, when the company’s sales perform better and which customers order specific products the most. ●● Help schools keep track of how many students they have registered, which subjects each student is taking and what their marks are per subject. ●● Help people or companies catalogue or track their products, for example, a store using a database to track when they need to reorder certain products and how often products are bought. ●● In the next section, you will be shown how to create a database based on a specific scenario. This will help you understand how databases can be used to solve problems. 212 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 212 2019/09/20 11:55 AM

UNIT 14.6 Designing databases for specific purposes Since databases can become incredibly complex, especially when you have many tables with many different fields, it is important to plan the structure of your database and what you will want it to do for you while you are using it. In Chapter 9 of this book, you learned about the basics of good database design and how to make sure your data was correct and complete. You were also shown how to sort that data into different tables. In Example 8.2, you will see how Access can be used to solve everyday problems based on the knowledge you have of the program. Mandla’s movie collection Mandla loves classic horror films and he has 200 classic horror movies in his DVD collection. He wants to find a way of keeping an easy record of what is in his collection. Until now, he has been using an Excel spreadsheet, but as he adds more movies to his collection, it is becoming unmanageable. Especially considering he has special editions of some movies, so he has duplicates in his spreadsheet. Mandla decides he wants to create an Access database that will keep track of the films he has. He looks at his spreadsheet and determines that movies all have the following things in common: ●● A director ●● A title ●● A movie studio He decides that these three items will become his tables. He starts by taking his Excel spreadsheet and breaking the data down. He copies all the directors’ names into a new sheet and removes the duplicates, reducing his 200 entries to just 50. He decides to add a small biography to each director’s name. Next, he looks at the movie studios and goes through the same process as he did for the directors. He knows that he will need to link directors to films and films to studios, so those will need primary keys in his database. Since director and studio names will be in text, he decides that those fields will be Short Text fields and the directors’ biographies will be Long Text/Memo fields. For his movies, he decides he wants to list the titles and release dates in his “Movies” table so he will need a Short Text field and a Date/Time field. He also wants to add a Yes/No field to show if the movie is a special edition or not. He now has the basic structure of his database in place and can build it. Once he is done building his database, he can link the tables together using the primary keys in the “Directors” table and the “Studios” table. He can also create queries that look for movies directed by the same director, or movies that were released by the same studio in the same year. With his database, Mandla is now able to keep proper track of films he does and does not have. term 4 I chapter 14 Reports I Unit 14.6 Designing databases for specific purposes 213 Gr11_CAT_Practical_LB.indb 213 2019/09/20 11:55 AM

REVISION ACTIVITY WRITTEN ACTIVITY 1. Explain the difference between Design View and Datasheet View of a table. (2) 2. Why are reports useful tasks a database can do? (2) 3. Complete the following table on the basic calculations you can use in a report. (4) CALCULATION DESCRIPTION FUNCTION (a) The sum of all the numbers in the column. Sum() Average The average value of all the numbers in the column. (b) Count (c) Count() Maximum The highest numeric or alphabetic value in the column. (d) (e) The lowest numeric or alphabetic value in the column. Min() 4. Which delimiter should you use in a *.csv file to make importing easier? (1) 5. Which view do you use to apply custom formatting to a database? (1) 6. Which other applications can you integrate Access with? (4) 7. List two things you can do to troubleshoot errors in Access databases. (2) 8. Describe the three ways you can access the Help pane in Access. (4) PRACTICAL ACTIVITY A database contains data about blood donors in schools. Open the Chapter 14_Revision Activity database and do the following: 1. Open the Info table and change the following: a. Change the field size of the Name field to 20. (1) b. Set the default value for the Grade field to 10. (1) c. Add a new field called Gender, using an appropriate data type, so that it appears immediately after the Grade field. (3) d. Add a validation rule to the Gender field so that only a “M” or “F” can be entered. If a user makes a mistake the following validation text should appear, “only M or F allowed”. (4) e. Add an appropriate feature that will allow the Weight field to accept data entered in the following way: ●● 2 compulsory numbers followed by KG. ●● EXAMPLE: 85KG, 50KG (3) Save and close the Info table. 2. Create a query called qryGrade based on the Info table. ●● Display only the Surname, Name, Grade and Category fields. ●● Sort the records alphabetically according to the surname. ●● Show the records of all the donors who are in grade 10 and grade 11. (4) Save and close the qryGrade query. 3. Create a form frmDonors based on the Info table. ●● Select only the following fields: Name, Surname, Age, Category and Blood Type. ●● Place a new label in the form header and type in ‘Donor Info’. Change the font size of the heading to 14pt and change the background colour of the heading to yellow. ●● Add the date and time to the Form Footer. (6) Save and close the frmDonors form. … continued 214 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 214 2019/09/20 11:55 AM

REVISION ACTIVITY … continued 4. Create a report called rprtBlood Donated based on the Info table. ●● Display only the Name, Surname, Grade, Weight and Litres Donated fields. ●● Group the records according to the Grade field. ●● Sort the records according to the Surname field. ●● Add the following functions in the report footer: ●● Insert a function to determine the average litres of blood donated by all the learners. ●● Insert a function to determine the total number of learners. (8) Save and close the rptBlood Donated report. Save and close the database. Total: [50] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Design basic reports that include calculations and headers and footers? 2 Import and export data to and from an Access database? term 4 I chapter 14 Reports I Unit 14.6 Designing databases for specific purposes 215 Gr11_CAT_Practical_LB.indb 215 2019/09/20 11:55 AM

TERM 4 CHAPTER INTRODUCTION TO HTML 15 CHAPTER OVERVIEW Unit 15.1: Introduction to HTML Unit 15.2: Basic HTML tags Unit 15.3: Text formatting Unit 15.4: Putting it all together By the end of this chapter, you will be able to: ●● Describe what HTML is and what it is used for. ●● Discuss what an HTML editor is. ●● Define HTML syntax. ●● Explain the structure and design of a simple HTML page. ●● Identify and use basic HTML tags, including document and text tags. ●● Use HTML comments. ●● Identify and use the correct attributes and syntax for HTML links, images and lists. ●● Describe the principles of good website design. INTRODUCTION While you may not have learned anything about HTML in Grade 10, you learned about some key concepts related to this topic. To refresh your memory, the Internet is the underlying network connecting computers, servers and people all around the world. The World Wide Web, on the other hand, is what we think about when someone talks about the internet. The World Wide Web, also known as the WWW, is all the web pages, pictures, videos and other online content that can be accessed through a web browser. A website is a web page, or collection of web pages, that contains specific information provided by a single source. The pages all share the same uniform resource locator (URL), like www.en.wikipedia.org, and have a similar design and theme. Web pages are made up of text, pictures, audio clips, video clips, animations and other media. Web pages are linked to each other on a website using hyperlinks. A hyperlink is a connection between one section of the document and another (like the hyperlinks on page numbers in the Table of Contents in a Microsoft Word document) or to another document entirely. 216 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 216 2019/09/20 11:55 AM

A URL is the website’s address. It is basically the unique place on the WWW where the website is kept, sort of like your house or school’s street address. In this chapter, you will learn all about what HTML is and how you can use it to create websites. You will be shown what an HTML editor is and how they work. You will also be introduced to HTML syntax and the structure and design of a simple web page. You will then be taught about the basic building blocks of HTML, specifically basic document tags, text element and formatting tags and HTML comments. You will also learn how lists, images and links are indicated in HTML. Lastly, you will learn about what makes a good website and what you will need to keep in mind when designing and building a website. term 4 I Chapter 15 Introduction to HTML 217 Gr11_CAT_Practical_LB.indb 217 2019/09/20 11:55 AM

UNIT 15.1 Introduction to HTML Every website, regardless of what it is used for, uses HTML. It is the foundation of the entire World Wide Web and the backbone of every website and web page. Understanding what HTML is, how it works and how you can use it is an essential skill in the internet age. In this unit, you will learn about HTML and HTML editors. You will also learn how HTML is structured (its syntax). You will use this knowledge and build on it in the other units in this chapter. WHAT IS HTML? HTML stands for HyperText Markup Language. This may seem confusing and like it tells you nothing, so let us break down what each portion means on its own: ●● HyperText refers to the fact that HTML allows you to click on links on the web page so you can move from one place to another in a web page (like the hyperlinks in a standard Microsoft Word document). ●● Markup Language simply means that normal English is marked to indicate things in a way that web browsers understand. WHAT IS AN HTML EDITOR? An HTML editor is a text editor (that is, a program on your computer) to help make writing HTML easier. The HTML editor Notepad++ is commonly used for writing HTML. Notepad++ can be downloaded and used for free. Other free text editors that could be used are, for example, Brackets and Atom. Figure 15.1: Notepad++ Using Notepad++ means that you do not have to download or buy a specific HTML editor. It is a more sophisticated text editor than Notepad which is included with the Windows operating system. It uses colours for your HTML code which allow you to identify errors. To save web pages in Notepad++, you must change the format from *.txt to *.html. 218 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 218 2019/09/20 11:55 AM

There are many other HTML editors such as Adobe DreamWeaver, KompoZer and Microsoft Expression Web available. These allow you to design web pages without using HTML. In CAT you are expected to write the HTML yourself. HTML SYNTAX HTML syntax is the system of rules governing how tags should be used and in which order. It is, essentially, the grammar rules of HTML. If the syntax of a tag is incorrect or not used correctly, your website could break and might not display correctly. This can cause major problems in more complex sites where pages depend on each other to load or work correctly. The syntax helps you define where something starts and ends. All HTML elements can be found in pairs inside angled brackets (< >). These are called tags. For a web page to display correctly in a browser, the elements and tags must give the browser TO FIND OUT MORE the correct instructions on what to display and where. HTML syntax describes where each tag ABOUT THE W3C must be and if they need to be inside other tags. Links, lists, images, etc. all have their own STANDARDS specific syntax to let the browser know what to display and how to display it. HTML syntax is standardised and The World Wide Web Consortium (W3C) develops, publishes and maintains those standards. There have been many versions of HTML since the beginning of the WWW and each version has been updated as web technology has advanced. The current set of HTML standards is called HTML 5. In CAT you will be learning an earlier version of HTML. This will prepare you well to move to https://www.w3.org/ the newer version if you need to. standards/ term 4 I Chapter 15 Introduction to HTML I Unit 15.1 Introduction to HTML 219 Gr11_CAT_Practical_LB.indb 219 2019/09/20 11:55 AM

UNIT 15.2 Basic HTML tags In order to build a website, you will need to know what the different HTML tags do so that you can give web browsers the correct instructions on how and what to display and in which order to display it for your website. In this unit, you will learn about the structure of an HTML page, opening and closing tags, and what they do and basic document tags, like <html>, <head>, <body> and <h> and where they are used in a web page. You will also learn about text element and formatting tags and how to use them. Lastly, you will learn about HTML comments and how and why they are used on a web page or website. OPENING TAG AND CLOSING TAG Before you can learn about the different tags, you need to understand the structure of a tag. All HTML tags come in pairs, the first one is known as the opening tag and the second one is the closing tag. Tags are enclosed by angle brackets and the opening and closing tags look the same. The only difference is that the closing tag has an added forward slash (/) after the first angled bracket. <h1>Closing and opening tags are cool</h1> The green tag above is the opening heading 1 tag and the red tag is the closing heading 1 tag. Pairs of tags with content between them such as the example above, are sometimes referred to as elements. BASIC DOCUMENT TAGS The basic document tags are used to set up the structure of a web page. They will all appear on every web page which you create using HTML. The table below shows the basic tags and briefly explains what their purpose is. TAGS FUNCTION <!DOCTYPE html> <!DOCTYPE html> tells the web browser that you are using HTML 5. It is not required for CAT and can be omitted. <html> </html> Tells the browser that this is an HTML page. <head> </head> This element contains meta information about the document. This can include a page title, keywords, the author of the document, when the document was last updated, etc. <title> </title> Specifies a title for the document. The title will be displayed on the browser tab. These tags must be written inside the <head> </head> tags. <body> </body> Defines the body of the web page; contains all the visible page content. Tags to display text, images, backgrounds and videos on the page are all found between the <body> </body> tags. 220 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 220 2019/09/20 11:55 AM

Guided Activity 15.1 1. Open a blank Notepad ++ document and type in the following code: <!DOCTYPE html> <html> <head> <title>A title</title> </head> <body> Hello World <body> <html> 2. Save this web page as HelloWorld.html. 3. You will not be able to open it in a browser if you have not saved the file as a *.html file. HEADING ELEMENTS Heading tags are used to display text as headings. These tags are like heading styles used in a word processor. The table below shows the heading tags: TAG FUNCTION <h1> </h1> Displays a level 1 heading. This is the main heading of your web page and should only be used once on each web page. <h2> </h2> Displays a level 2 heading. These, and the other heading levels, are used to display sub-headings. They can be used more than once on each web page. <h3> </h3> Displays a level 3 heading. <h4> </h4> Displays a level 4 heading. <h5> </h5> Displays a level 5 heading. <h6> </h6> Displays a level 6 heading. Guided Activity 15.2 1. Type the following code into Notepad++. Make use of the TAB key to indent your code correctly. … continued term 4 I Chapter 15 Introduction to HTML I Unit 15.2 Basic HTML tags 221 Gr11_CAT_Practical_LB.indb 221 2019/09/20 11:55 AM

Guided Activity 15.2 … continued 2. Save the file as Headings.html. 3. Open the file Headings.html in a web browser such as Google Chrome or Microsoft Edge. Right-click on the file name, click on Open With and select the browser of your choice. 4. Your browser display should look the figure below: Notice that all headings are displayed in bold and with space above and below them. A level 1 heading has a bigger font size than a level 2 heading, and so on. You should also see the web page title, “Heading Tags”, displayed in the browser tab. In most web pages, only the first three heading levels are used. TEXT ELEMENTS Text elements are used to display all text on your web page other than headings. The table below shows the text elements that you need to know: TAGS FUNCTION <p> </p> Displays a paragraph of text. A line is left open after each paragraph. Most text on a web page is displayed using <p> tags. <br /> This is a line break tag. It displays the next line of text on a new line but without a space above it. This tag is one of the few tags that does not come in pairs. The closing tag forms part of the opening tag. <hr /> This tag displays a horizontal line. (hr stands for horizontal rule.) This tag is used to separate sections of your web page or underneath heading tags to make them stand out more. This tag also does not come in pairs as is never used to display text. 222 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 222 2019/09/20 11:55 AM

Guided Activity 15.3 1. Type the following code into Notepad++. Make use of the TAB key to indent your code correctly. 2. Save the file as text.html. 3. Open the file text.html in a web browser. 4. Your browser display should look like the figure below: Take note of the difference in the way the two paragraphs are displayed. TEXT FORMATTING There are two tags that are used to make parts of a paragraph stand out. These tags are explained in the table below: TAG FUNCTION <b> </b> Used to display text in bold. <i> </i> Used to display text in italics. Both tags are often used inside a pair of <p> </p> and are applied to important word or words that need to be emphasised. Because of this they are sometimes known as inline tags. term 4 I Chapter 15 Introduction to HTML I Unit 15.2 Basic HTML tags 223 Gr11_CAT_Practical_LB.indb 223 2019/09/20 11:55 AM

Guided Activity 15.4 1. Open the file text.html which you saved after the last Guided Activity in Notepad++. 2. Make the following changes to the HTML code: a. Display all occurrences of the word “fox” in bold. b. Display all occurrences of the word “dog” in italics. 3. Your code should now look like this: 4. Save the file as text2.html. 5. Display the file in a web browser. Notice how all the words “fox” appear in bold and all the words “dog” appear in italics. Do not close the browser tab. Something to know In HTML 5 the <b> and <i> tags are no longer used. HTML is only used to 6. Delete the closing tag </b> after the first “fox”. Save the file again. Switch to the browser and display the content of a web refresh the web page. Either click on the refresh button or press the function key F5. page and to indicate its 7. What happened to the way the text was displayed? meaning. Formatting is Notice that omitting a closing tag can cause your web page to display in unexpected ways. In done using Cascading Style Notepad++ if you click on an opening tag it should be highlighted with a coloured background. Sheets (CSS). CSS has a The closing tag should also be highlighted. completely different syntax <b>fox</b> than HTML and is more difficult to learn. If the opening tag is not highlighted, then you know that the closing tag is missing. 224 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 224 2019/09/20 11:55 AM

HTML COMMENTS When you want to make a note to yourself in your HTML, you can use the comments tag. Comments allow you to keep track of what you have done or to remind you to do something without displaying that information on the website. Comments tags can be inserted anywhere in the HTML source code. You can also use comments to find errors in your code (this process is called debugging), by commenting out one line of code at a time to see which one is actually causing the problem. You can add comments by using this tag:  Note that there is an exclamation mark (!) in the opening tag, but not the closing tag. Activity 15.1 PRACTICAL ACTIVITY You are going to create a web page where you will display information about yourself. Type the HTML code in Notepad++. Save the file as Act 15_1.html. It is a good idea to save your file after each step. View what you have done in a browser to check that you have not made any mistakes. Do not close the browser. Save any changes you have made in Notepad++ and then refresh the browser window to update the display. 1. Create the basic structure of the web page using the correct document tags. Give the web page the title “Personal Information”. 2. In the body of the web page do the following: a. Display the text “All about me!” as the main heading on the page. b. Display the text “Favourite sports” as a level 2 heading. Display a horizontal line under this heading. c. Write the names of two sports you enjoy doing or watching under this heading. The name of the sports must be in a paragraph but each one must display on a new line. d. Display the text “Favourite subject” as a level 2 heading. Display a horizontal line under this heading. e. Write two short paragraphs explaining why the subject is your favourite. f. Include the name of the teacher who teaches this subject in one of the paragraphs. The teacher’s name should be displayed in bold. g. At the end of the document, just before the closing tag, type in “Created by [your name]”. Use a tag to ensure that this text is NOT displayed in the browser. 3. Save and close the file Act 15_1.html. term 4 I Chapter 15 Introduction to HTML I Unit 15.2 Basic HTML tags 225 Gr11_CAT_Practical_LB.indb 225 2019/09/20 11:55 AM

UNIT 15.3 Text formatting In the previous unit you probably noticed that your web pages are all rather boring. All the text is coloured black, the fonts are the same and everything is aligned on the left of the page. In this unit you will learn how to use attributes to make your web pages look more interesting. Attributes are added to opening tags and they are used to modify how the element displays on the screen. It is important to follow the correct syntax when using attributes to ensure that your web page displays correctly. ANATOMY OF AN ATTRIBUTE An attribute is made up of two parts: ●● The attribute name ●● The attribute values <p align = “center”>The quick brown fox</p> Note the following: ●● Attributes are ONLY added to opening tags. You should NEVER include an attribute in a closing tag. ●● Attribute values should be enclosed in quotes. In HTML 5 quotes are sometimes optional. You should always add quotes so that you do not have to remember when to use them. ●● Sometimes attribute values use American spelling, for example center (not centre) and color (not colour). ALIGNMENT ATTRIBUTES Alignment attributes are used with text and heading tags to control where the text is displayed on the page. The table below shows the different values that can be used: ATTRIBUTE VALUE FUNCTION align left Aligns the text on the left side of the page. This is the default alignment for most HTML elements, so it is seldom used. center Aligns the text in the centre of the page. (Note the spelling.) right Aligns the text on the right side of the page. The align attribute is used with the <p> tag as well as the <h1> to <h6> tags. It is also possible to align text elements (and others such as images) using the <center> </center> tag. The <center> tag must surround another element (enclosing both opening and closing tags). See the example below: <center><p>The quick brown fox</p></center> 226 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 226 2019/09/20 11:55 AM

Guided Activity 15.5 1. Type the following code into Notepad++. 2. Save the file as align.html. 3. View the file in a web browser. 4. If your web page does not display correctly, go back and check your code carefully. If you resize your browser window, the centred heading will still appear in the centre of the page. Notice that in Notepad++ the colours of the attribute and the attribute values, the text and the tags are all different. These colours only appear after you have saved the file as *.html. The colours will also not appear if, for example, you spell the attribute incorrectly. WORKING WITH FONTS The appearance of text can also be improved by using a different font, font size or font colour. Changing the font of text or headings requires the use of a new tag, the <font> </font> tag. This tag has its own attributes. The font tag is always used in combination with a <p> or <h*> tag. The font tag is written inside the <p> or <h*> tags. This is known as nesting. Look at the following example: <p><font color = “red”>Paragraph text</font></p> This combination of tags and attributes will display the paragraph text with a red colour. Notice that the tag which is opened first (<p>) is closed last (</p>). The tag which is opened second (<font>) is closed first (</font>). term 4 I Chapter 15 Introduction to HTML I Unit 15.3 Text formatting 227 Gr11_CAT_Practical_LB.indb 227 2019/09/20 11:55 AM

The attributes that can be used with the <font> tag are shown in the table below: ATTRIBUTE VALUE FUNCTION face Any valid font name such Changes the font that is displayed. as Arial or Comic Sans MS The correct name for the font can be found by looking at the Font dropdown in the Font group on the Home tag in MS Word. color Any valid colour name Displays the text in a different colour to the default black. such as red, blue or green. There are 140 standard colour names in HTML. size Values ranging from 1 to 6 Do the following activity to get a better understanding of how to use the <font> tag. Guided Activity 15.6 1. Type the following code in Notepad++ and save the file as fonts.html. 2. View the file in a web browser. 3. Your web page should look like this: 4. If your page does not display correctly, return to Notepad++ and correct your errors. Note the following: ●● The colour attribute must be spelled correctly, viz. color ●● A list of valid HTML colour names can be found on the W3 Schools website: https://www.w3schools.com/colors/colors_names.asp ●● More than one attribute can be used in the same font tag. It is not necessary to have a new <font> tag for each attribute. The order in which the attributes are added does not matter. 228 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 228 2019/09/20 11:55 AM

HORIZONTAL LINES There are two attributes that can be used for the horizontal lines’ appearance. The attributes are added to the <hr /> tag. They are described in the table below: ATTRIBUTE VALUE(S) FUNCTION size A number measured in pixels. The unit Changes the height (thickness) of the for pixels (px) does not need to be used. horizontal. Browsers display a hollow Pixels are the size of the picture line which can be difficult to see. elements on the screen. 1 cm is approximately 37,8 pixels. width Either a number in pixels, for Changes the length of the line. example 100 or a percentage, If the width of the line is given in pixels, for example 50%. the width will stay the same even if the browser window is resized. If the width is given as a percentage, the length of the line changes to that percent of the available space. If the browser window is resized, the width of the line will change. Guided Activity 15.7 1. Type the following code into Notepad++. Save the file as lines.html. 2. View the file in a web browser. You page should look this: … continued term 4 I Chapter 15 Introduction to HTML I Unit 15.3 Text formatting 229 Gr11_CAT_Practical_LB.indb 229 2019/09/20 11:55 AM

Guided Activity 15.7 … continued 3. Resize your browser window. Note that the line where the width is in pixels stays the same while the ones where the width is given as a percentage change size according to the width of the screen. Note the following: ●● It is often difficult to see that the width of the line has changed. You can use CSS to display a solid line. ●● Both attributes can be added to a single <hr> tag. The order in which you add the attributes does not matter. BODY BACKGROUND COLOUR Up until now all the web pages you have created have had a white background colour which is a bit boring. There is a useful attribute that can be used to change the background colour of your web page. This attribute is added to the opening <body tag. See the table below: ATTRIBUTE VALUE FUNCTION bgcolor Any valid Changes the background colour of the page to the one selected. colour name Choose your background colour carefully. People must always be able to read the text on the page. Guided Activity 15.8 1. Type the following code into Notepad++ and save the file as background.html. 2. View the page in a web browser. 3. Your page should display with a light yellow background colour. It is also possible to use a suitable image as the background of a web page. This is done using the background attribute. See the example below: <body background=”w3s.png”> 230 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 230 2019/09/20 11:55 AM

UNIT 15.4 Putting it all together In this unit you will be doing an activity where you will apply all the skills you have learned in this chapter. Activity 15.2 Study the screenshot of a web page below carefully. 1. Open a new file in Notepad++. Save the file as Act 15_2.html. 2. Add the basic document tags to create a web page. The text “Labrador Retriever” must appear on the browser tab. 3. Using the screenshot as a guide, add tags and attributes to the body to create a web page which is the same as the one in the screenshot. ●● The background colour of the page should be light yellow. ●● There is only one level 1 heading on the page. ●● The main heading uses the Comic Sans MS font. ●● The other headings use the Arial font. 4. Save your work regularly and preview it in a browser as you are working. term 4 I Chapter 15 Introduction to HTML I Unit 15.4 Putting it all together 231 Gr11_CAT_Practical_LB.indb 231 2019/09/20 11:55 AM

REVISION ACTIVITY WRITTEN ACTIVITY Study the following code: <html> <head> <title>Critical information</title> </head> <body> <center> <h1>Hello World!</h1> <p>A «Hello World!» is any computer program that outputs the text «Hello World!» to the user.</p> <p>Since this is a relatively easy task, it is often the first program that programmers learn to create.</p> <h2>History</h2> <p>The «Hello World!» program was first included as a beginner›s program in a book from 1972 called <i>A Tutorial Introduction to the Language B</i>.</p> </center> </body> <h2>Challenges</h2> <p>Programmers often like to challenge themselves by finding strange and funny ways to code “Hello World”, or to create it on strange devices, such as calculators.</p> </html> 1. Based on the code, answer the following questions in your own words. a. What is the title of the page? (1) b. How many headings are there on the page? (1) c. How many paragraphs of text are there? (1) d. What information should be placed in the body section? (1) e. Are there any lines of code that should be moved to the body section? (1) 2. Name two elements of an HTML document. (2) 3. Explain the difference between the World Wide Web and a website. (4) 4. Why would you use comments in a web page? (2) 5. Name three advantages of using formatting and structural elements in a web page. (3) … continued 232 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 232 2019/09/20 11:55 AM

REVISION ACTIVITY … continued PRACTICAL ACTIVITY 1. Use a text editor to create a web page using your knowledge of html elements and tags to give some info about South African School Sports. Follow the following guidelines to create a web page:(20) ●● Use Notepad to create a document. Save it as an HTML document. ●● The title of the web page is ‘School Sports’. ●● Create the following headings: ¡¡ Heading 1: Popular sport codes ¡¡ Heading 2: Summer sports ¡¡ Heading 2: Winter sports ¡¡ Heading 3: School policy with regards to sport ¡¡ All headings must be formatted in bold. ¡¡ All headings must have a horizontal line in between. ●● Write the following paragraphs after each heading: ●● P1: The most popular sports in South African schools are; rugby, netball, athletics, soccer and cricket. ●● P2: In South Africa athletics and cricket are played during the summer months. ●● P2: In South Africa netball, rugby and soccer are played during the winter months. ●● P3: Learners must be accompanied by an educator when they compete against learners from other schools. Save the web page and view it in a browser. Total: [36] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Describe what HTML is and what it is used for? 2 Discuss what an HTML editor is? 3 Define HTML syntax? 4 Explain the structure and design of a simple HTML page? 5 Identify and use basic HTML tags, including document and text tags? 6 Use HTML comments? 7 Identify and use the correct attributes and syntax for HTML links, images and lists? 8 Describe the principles of good website design? term 4 I Chapter 15 Introduction to HTML I Unit 15.4 Putting it all together 233 Gr11_CAT_Practical_LB.indb 233 2019/09/20 11:55 AM

TERM 4 CHAPTER PRINCIPLES OF WEB DESIGN 16 CHAPTER OVERVIEW Unit 16.1: HTML and web design Unit 16.2: HTML links and images By the end of this chapter, you will be able to: ●● Identify and use the correct attributes and syntax for HTML links, images and lists. ●● Describe the principles of good website design. INTRODUCTION Now that you understand the basics of how a website is made, you need to learn about what a website needs to look like. A well-designed website is one that is easy to navigate, easy to read and good to look at. In this chapter, you will learn about the basics of good web design and how HTML works to make a website look good and be user-friendly. You will also learn about HTML links, images, lists and the correct attributes and syntax for these elements. 234 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 234 2019/09/20 11:55 AM

UNIT 16.1 HTML and web design Websites have many purposes ranging from providing information to entertainment to e-commerce. You can have the most beautifully designed website in the world, but if it does not provide the information people are looking for quickly and easily, it will not be a good website. This is why good website design is important. A good website is one that looks good, loads quickly and is easy to navigate. In this unit, you will learn about the basics of good website design and how to use colour and basic text formatting to get the message across as quickly as possible. GOOD WEBSITE DESIGN Something to know If you wish to design a good website, there are a number of important things that should be considered: For a look at how website 1. The purpose of a website impacts on how it is designed. A website that provides design has changed since information will have a different design to an e-commerce website. the dawn of the internet 2. The website should be good to look at. This includes the layout of website elements, age, A Look Back at 20+ choice of fonts and use of colour. Headings, sub-headings and lists should be used to Years of Website Design organise information. has several good examples: https://blog.hubspot.com/ 3. The content of the website should be up to date and relevant to your target audience. marketing/look-back-20- Content should also be original and not plagiarised from another source. years-website-design. 4. It should be easy to navigate the site. It must be easy to identify hyperlinks and users should not have to click too many times to get where they need to. Many people use a mobile device such as a smartphone to view websites. You need to ensure that your website looks as good on a mobile phone screen as it does on a laptop or desktop computer. DESIGNING A WEBSITE OR WEB PAGE Page layout is one of the most important aspects of website design. You need to think about how you are going to be arranging the content on it. You need to start with laying out the information correctly and neatly. Placing content on the pages in a random way looks messy. Use a grid-based layout to arrange content into sections. Most people read from left to right and from top to bottom, so they naturally read things in the shape of a capital letter F. Most of what people see is in the top and left of a screen and the bottom right is very rarely looked at. Make sure the most important information is where people will naturally look first. You will also need to consider the spacing of the website. Using empty space (or negative space) balances out the page and draws the eye to the most important content. A lot of negative space also makes things look elegant and clean. term 4 I Chapter 16 Principles of web design I Unit 16.1 HTML and web design 235 Gr11_CAT_Practical_LB.indb 235 2019/09/20 11:55 AM

Figure 16.1: Apple’s website uses negative space very effectively Lastly, each page on the website must look like it belongs with the rest. You should carry specific design elements (like colour, logos and font) through to all the pages. USING COLOUR Colour is important in web design. Colour draws the eye and makes important things stand out. Colours should be used to enhance the purpose of your website. Information must be clear and easy to read. Colours need to be chosen for the background of the page, important information and body text. If the website is providing information about a company or business the colours of the company logo could be used. If you are designing a website, the colours used should be consistent across different pages. Don’t choose a different background colour for each page on your site. In CAT you will add colour to your website using attributes linked to tags. Most modern websites, however, use Cascading Style Sheets to add colours. If you are interested in finding out more about CSS there are a number of free online courses available. BASIC TEXT AND TEXT FORMATTING Finally, you need to think about the text. Ask yourself: ●● Will you be using plain text or a specific font? ●● How will the text be formatted and where will you use <b> and <i> tags to draw attention to specific things? ●● How many heading levels will you have and how big will they be? ●● How will the text be spaced out? ●● Will the text be a different colour? ●● What colour will my links be? Once you have all of that figured out, you can start creating your website. 236 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 236 2019/09/20 11:55 AM

Activity 16.1 Written activity. Look at the screenshots taken from two different online stores. Using your knowledge of good and bad website design, answer the questions below. 1. Name four things which make Arngren a badly designed website? 2. Explain why the second screenshot of Amazon is a better designed site? 3. What could Arngren do to improve on their website design? term 4 I Chapter 16 Principles of web design I Unit 16.1 HTML and web design 237 Gr11_CAT_Practical_LB.indb 237 2019/09/20 11:55 AM

UNIT 16.2 HTML lists, links and images In this unit you will learn about some new tags which are used to make web pages more interesting. Lists are used to organise information. Images add interest to a web page. Hyperlinks are key to all websites, allowing you to jump to different places with a click of your mouse. HTML LISTS You can use lists to organise the information on an HTML page. For example in a word processing program, you can have numbered lists and bulleted lists. Numbered lists are also called ordered lists and bulleted lists are called unordered lists. The tags used to create these elements are described in the table below: TAG FUNCTION <ul> </ul> Displays a bulleted (unordered) list on the page. <ol> </ol> Displays a numbered (ordered) list on the page <li> </li> Each item in the list must be enclosed in a pair of <li> </li> tags. Guided activity 16.1 1. Type the following code into Notepad++ and save the file as lists.html. 2. View the file in a web browser. … continued 238 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 238 2019/09/20 11:55 AM

Guided activity 16.1 … continued 3. Note how the li> tags are nested inside the <ul> tags as well as the use of indentation. This is done to ensure that the HTML is easy to read. 4. Replace the <ul> tag with <ol> and the </ul> tag with </ol>. Save the file and view the results in a browser. 5. Notice how the bullets have changed to numbers. There is an attribute, called type which can be used to change the type of bullet or the type of number used in a list. The values which can be used with the type attribute are described in the table below: UNORDERED LISTS (<UL>) Attribute Value Function type disc Changes the bullet to an open circle square Displays a square bullet circle Display a bullet which is a solid circle. This is the default value which is displayed if the type attribute is not used. NUMBERED LISTS (<OL>) Attribute Value Function type 1 Displays a list numbered from 1 onwards. This is the default attribute which does not have to be used. A Displays an alphabetical list with uppercase letters, e.g. A., B., C., … a Displays an alphabetical list with lowercase letters, e.g. a., b., c., … I Displays a list using uppercase Roman numbers, e.g. I, II, III, … i Displays a list using lowercase Roman numerals, e.g. i, ii, iii, … There are two other attributes which can only be used with ordered lists, reversed and start which you can find out about if you want. term 4 I Chapter 16 Principles of web design I Unit 16.2 HTML lists, links and images 239 Gr11_CAT_Practical_LB.indb 239 2019/09/20 11:55 AM

Guided activity 16.2 1. Open the file lists.html which you saved in Guided Activity 16.1. 2. Make the changes below to the HTML code and save the file: 3. View the code in a web browser. Notice how the bullet has changed to a square shape. 4. Change the value of the type to “disc”. Save the file and view the results in a browser. Notice how the bullet has changed to an open circle. 5. Change the unordered list to and order list. 6. Change the type attribute to display a numbered list like the one below. Save the file before viewing in a browser. 7. Experiment with different values of the type attribute and view the results in a browser. HTML LINKS The ability to use hyperlinks to move easily from one place in a document to another or from one document to another is one of the reasons the HTML markup language was created. In this section we will show how to: ●● Link to another website or another page in a website ●● Link to a file ●● Link to a different place in the same document. All HTML links are hyperlinks, meaning that they allow you to jump from page to page in a document or to another document entirely. When you move your mouse over a link, the cursor changes from an arrow into a small hand with a pointing finger. Regardless of what type of link it is, all links follow the same basic structure, for example: <a href=“http://www.facebook.com”>This is the link</a> 240 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 240 2019/09/20 11:55 AM

The tag for links is called the anchor tag and it does have an opening tag and a closing tag, it just looks slightly different than all the other tags you have seen so far. Without the destination address and the reference in it the anchor tag is <a></a>. The “href” attribute inside the opening anchor tag specifies the destination address (the URL) of the link, telling the browser where to take you if you click on it. The destination address must always be in double quotation marks (“_”). The text between the tags is called the anchor text. This text should be highlighted in some way so that users of the page know where to click. The following are the default colours used in all browsers: ●● Blue and underlined for an unvisited link ●● Purple and underlined for a visited link ●● Red and underlined for an active link (this happens when you click on the link) These colours can be changed using CSS. Guided activity 16.3 1. Type the code below into Notepad++. Save the file as links1.html. Something to know The &lt and &gt are special codes (called attributes) which are used in HTML to display a less than sign (<) and a greater than signs (>). If you type <a> into HTML, the browser is 2. View the page in a browser. expecting a hyperlink and 3. Click on the word link, which should be blue and underlined. Make sure the W3 Schools will not display the webpage opens correctly. If not, check that you did not making a typing error. <a> correctly. In the previous Guided Activity, you will have noticed that the new web page replaced the web page you were viewing. You must click the Back button on the browser to go back to where you were. Many users find this annoying. To force the new website to open in a new tab on your browser, the target attribute is used. <a href=”http://www.google.co.za target = “_blank”>Go to Google</a> There are a few other values for the target attribute which are sometimes used in older websites. It is not necessary for you to know them. If a web page contains a lot of information, links are often used to jump from one part of the page to another. These are called internal links. term 4 I Chapter 16 Principles of web design I Unit 16.2 HTML lists, links and images 241 Gr11_CAT_Practical_LB.indb 241 2019/09/20 11:55 AM

Creating an internal link is done in two steps. 1. Create the place you want to jump to by making a bookmark: <a id=”Place”>Bookmark text</a> The id attribute is used to give the bookmark a name. The bookmark name should be a single word and must not contain spaces. In older versions of HTML the attribute name was used instead of id. You can replace the id attribute with name without affecting your webpage. 2. Add a hyperlink to the bookmark at the place you wish to jump from: <a href=”#Place”>Go to Place</a> The bookmark name has a # in front of it. “Go to Place” will be the anchor text and will be blue and underlined. In the following Guided Activity, we will learn how to create internal links. Guided activity 16.4 1. Open the file links2.html, which you will find in your data folder, in Notepad++. 2. Change the level 2 heading “Part Two” as follows: <h2><a id=”Part2”>Part Two</a></h2> 3. Save the file and view it in a browser. You should not notice any change. 4. Make the following changes to the top of the document: 5. Save the file and view it in a browser. Click on the hyperlink “Part 2” which you have just created and observe what happens. You might need to make the browser window smaller to better see what happens. 6. Add a bookmark to the level two heading “Part Three”. Call the bookmark id “Part3”. 7. Add the paragraph with text “Go to Part 3” under the paragraph you added in Question 4. Hyperlink this paragraph to the “Part3” bookmark. 8. Save the file in the browser and ensure that the hyperlink works correctly. HTML IMAGES Images can add to the design and appearance of a web page. Images also have their own unique syntax, starting with their tag. Images are defined with the <img> tag. This tag is empty, meaning it does not have a closing tag. To see how an image is specified in HMTL, look at the example below: <img src=“kitten.jpg” alt=”My new kitten” /> 242 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 242 2019/09/20 11:55 AM

The image tag is a map of where the image fits on a page since images are not technically inserted directly into an HTML page. Rather, images are linked to a folder (either the same one as the page or a sub-folder) or to a server or website. The <img> tag creates a holding space for the referenced image. The image tag is an example of what is known as an inline element. Text and other elements wrap around the image. There are two attributes that should be used for every image: ●● Source (src) ●● Alternate text (alt) The source attribute indicates where an image is stored. This is either the folder on the website or another website or server. You need to specify where the image is stored in order for it to display. You also must give the correct file extension, e.g. jpg or bmp. For example, the src attribute below indicates the pictures folder on a website: <img src=“images/kitten.jpg” /> The alt attribute gives alternate text for an image. This is a short description of what the image shows so that if a user cannot view the image because the file is no longer available, or they are using a screen reader, they can still know what the image looks like. <img src=“images/kitten.jpg” alt=”small black kitten” /> There are other attributes which can be used with the img tag. They are described in the table below: ATTRIBUTE VALUE(S) FUNCTION height A number (in pixels) Allocates the size needed for the height of the image before it is loaded. width A number (in pixels) Allocates the size need for the width of the image before it is loaded. If the value of only one of height or width is specified, the browser will adjust the other attribute so that the image’s aspect ratio stays the same. If you specify both values, you must be careful to not distort the image by changing the aspect ratio. ATTRIBUTE VALUE(S) FUNCTION align left Aligns the image on the left of the page. This causes text (and other elements) to wrap around the right-hand side of the image. right Aligns the image on the right of the screen. The causes text (and other elements) to wrap around the left-hand side of the image. top Aligns the top of the image with surrounding elements middle Aligns the middle of the image with surrounding elements bottom Aligns the bottom of the image with surrounding elements term 4 I Chapter 16 Principles of web design I Unit 16.2 HTML lists, links and images 243 Gr11_CAT_Practical_LB.indb 243 2019/09/20 11:55 AM

Note that the value “center” does not work with an image. An image can, however, be centred using the <center> tag introduced in Chapter 15. See the example below: <center><img src=w3s.png” alt=”Logo” /></center> Guided activity 16.5 1. Open the file images.html in Notepad++. 2. Type in the following code underneath the heading “Images”: <img src=”cube.jpg” alt=”A cube” /> 3. Save the file and view it in a browser. The image should appear under the heading and the following paragraph starts under the image. 4. Add align=”left” to the image tag: <img src=”cube.jpg” align=”left” alt=”A cube” /> Save the file and view in the browser. The image is now on the left of the screen and the text wraps around the image. 5. Now change the value of the align attribute to “right”. Save the file and view in browser. Take note of the result. 6. Add the code <img scr=”tinycube.jpg” align=”top” alt=”A small cube” /> immediately after the first <p> tag. 7. Save the file, view in the browser and take note of what happens. Now change the value of the align attribute to “middle”. Save the file again. View it in the browser and take note of how the position of the image changes. 8. Finally change the value of align to “bottom”. Save the file and view the changes. REVISION ACTIVITY A web page has been created to give some first-hand information to visitors who are totally unaware of uShaka Marine World and the unlimited fun it has in store for them. Open the incomplete uShaka web page in a web browser and also in a text editor, e.g. Notepad. NOTE: Question numbers are inserted as comments in the coding as guidelines to show approximately here the answer(s) should be inserted. … continued 244 COMPUTER APPLICATIONS TECHNOLOGY I GRADE 11 I Practical Book Gr11_CAT_Practical_LB.indb 244 2019/09/20 11:55 AM

REVISION ACTIVITY … continued The final web page should look like the example below. 1. Add the HTML code so that the text Unlimited Fun@uShaka appears in the browser tab. (2) 2. Change the background colour of the web page to yellow. (1) 3. Change the font of the whole web page to Century Gothic. (2) 4. Correct the HTML code to display the heading “Unlimited Fun for EVERYONE!” in heading 1 style. (1) 5. Insert a horizontal line after the heading “Unlimited Fun for EVERYONE!” and before the last line of the web page starting with “For more information …” ●● The colour of the horizontal lines should be red. ●● The size should be 4. (3) 6. Locate the items under the heading Sea World. Make the following changes: ●● Display it as an ordered list. ●● The list should be in the format A,B,C. (2) 7. Insert the image Fun Unlimited.jpg before the heading “Wet ‘n Wild” and do the following: ●● Set the height of the image to 50% and width to 25% respectively. ●● Align the image to the right. ●● Insert an alternative text to display Unlimited Fun if the image does not display. (6) 8. Display the sentence “call 031 328 8000” found at the bottom of the web page to be italics. (1) 9. Link the web address found at the bottom of the web page that reads www.ushakamarineworld.co.za to the same web address. (2) Total: [20] AT THE END OF THE CHAPTER CAN YOU ... YES NO 1 Identify and use the correct attributes and syntax for HTML links, images and lists? 2 Describe the principles of good website design? term 4 I Chapter 16 Principles of web design I Unit 16.2 HTML lists, links and images 245 Gr11_CAT_Practical_LB.indb 245 2019/09/20 11:55 AM

Gr11_CAT_Practical_LB.indb 246 2019/09/20 11:55 AM

Gr11 CAT Practical LB print 20 9 19 hlayiso.com

Recommended for this subject

Gr11 CAT Practical LB web 20 9 19 hlayiso.com

Gr11 CAT Theory LB print 19 11 19 hlayiso.com

CAT Grade 11 Teacher's Guide v2 hlayiso.com

Gr11 CAT TG Study Guide LR hlayiso.com

Gr 11 Comp App Tech ATP 2023 24

CAT P1 MEMO GR11 NOV 2022 Afrikaans hlayiso.com

CAT P1 MEMO GR11 NOV 2022 English hlayiso.com

CAT P2 GR11 MEMO NOVEMBER 2022 English hlayiso.com

CAT P1 QP GR11 NOV 2022 English hlayiso.com

CAT P2 GR11 QP NOV2022 English hlayiso.com

CAT P2 GR11 MEMO NOVEMBER 2022 Afrikaans hlayiso.com

CAT P1 QP GR11 NOV 2022 Afrikaans hlayiso.com

More from Grade 11 Computer Applications Technology

CAT P2 GR12 QP NOV2022 Afrikaans hlayiso.com

CAT P1 QP MEMO GR11 NOV 2020 AFR D hlayiso.com

CAT P1 MEMO GR11 NOV 2020 AFR D hlayiso.com

CAT P1 MEMO GR11 NOV 2020 ENG D hlayiso.com

CAT P1 QP MEMO GR11 NOV 2020 ENG D hlayiso.com

CAT P2 GR11 QP MEMO NOV2020 AFR D hlayiso.com