midterm project for ( data and computing skills ) course you have to work on excel sheet and the exam is excel based project – Download files and complete all tasks that are written on the Midterm P

midterm project for ( data and computing skills ) course you have to work on excel sheet and the exam is excel based project – Download files and complete all tasks that are written on the Midterm P
C ONDITIONAL F ORMATTING Formatting allows you to change the way that the data in cells within a worksheet appears on the screen. For example, numbers can be made to appear as currency values or percentages by formatting them accordingly. Excel provides a variation on formatting known as conditional formatting . With conditional formatting, cells can be formatted in different colour schemes. Rather than this formatting being applied to all cells in a range, it is applied selectively and based on specific rules. This type of formatt ing allows you to see, for example, values that are over a certain amount, or to instantly spot high and low values based on assigned colouring. U NDERSTANDING C ONDITIONAL FORMATTING As the name suggests, conditional formatting is a type of formatting tha t is applied to cells or ranges when certain conditions are met. These conditions are set, but can quite often be customised and edited, in rules that have been programmed into Excel. There are two types of conditional formatting – values -based formatting and trend -based formatting What Happens With Conditional Formatting With conditional formatting , cells in a specified range are coloured or shaded according to certain conditions which are outlined in rules . Values -Based Conditional Formatting With values -based conditional formatting, cells in the range are examined and their shading and colouring is based on whether they meet the conditions of the rule. This type of formatting allows you to see whether values in a range are greater than a certa in value, less than a certain value, equal to a certain value, or fall between ranges. You can also display the top ten , bottom ten , top 10% , bottom 10% , and above and below averages with this type of formatting. In all cases a dialog box will appear whic h allows you to modify the rule based on what is required. Basically, the dialog box allows you to specify a value or a range of values for the rule, and to determine the colour of the shading used. Below is an example of the dialog box for applying the Be tween rule. Trend -Based Conditional Formatting With trend -based conditional formatting, colouring is applied to all of the cells in the range. The depth of the colouring is determined by the values shown in each cell relative to the overall total of th e range. This allows you to instantly spot higher, lower and median values in the range and to see the trend of the numbers. The formatting can be applied in the form of coloured bars , coloured scales and even icons . F ORMATTING C ELLS C ONTAINING V ALUES A common analysis requirement is to see what values in a worksheet are greater than a specific amount. For example, you may want to see which salespeople have achieved better than their set targets. This can be done using the Greater Than option which appears under Highlight Cell Rules in the Conditional Formatting menu on the Home tab. TASK: Before starting this exercise you MUST open the file Conditional Formatting_1.xlsx 1. Select the range I4:I45 in the Used Vehicle Sales worksheet. 2. Click on the Home tab, click on Conditional Formatting in the Styles group, then point to Highlight Cells Rules to display a menu of options, as shown 3. Select Greater Than to display the Greater Than dialog box. With Live Preview, the cells in the range that meet the condition are highlighted… 4. In Format cells that are GREATER THAN: type 90000. Notice how the formatting changes. 5. Click on the drop arrow for with , then select Green Fill with Dark Green Text to change the colour of the shading 6. Click on [OK] to apply the formatting 7. Click in a blank cell to deselect the range and see the formatting more clearly Note: The Greater Than conditional formatting option is very literal. If, for example, you ask it to format values over 90,000 it will only format values that are over 90,000 – any value of 90,000 will not be formatted. You can clear the conditional formatting by clicking on th e Home tab, then in the Styles group, selecting Conditional Formatting > Clear Rules. M ORE C ELL F ORMATTING O PTIONS There are a number of options under Highlight Cells Rules on the Conditional Formatting menu that are handy to know about and use. These in clude the ability to format less than a value, format for values between two values, and format for values equal to a specific value. TASK: (This is for your understanding) 1. Select the range C4:H10 in the Used Vehicle Sales worksheet . This includes all of the sales for BMW motor vehicles… 2. Click on the Home tab, click on Conditional Formatting in the Styles group, then point to Highlight Cells Rules to display a menu of options 3. Select Less Than t o display the Less Than dialog box 4. Type 15000 to see how many months had model sales less than 15,000. At this stage we want to use more of these commands so we’ll cancel the previous one… 5. Click on [Cancel] to cancel the formatting 6. Repeat steps 2 to 5 to see the formatting for the Between setting and the Equal To setting 7. Click on [Cancel] to cancel the formatting Note: The task called More Cell Formatting Options is for your understanding regarding other cell formatting options concept. By doing that part you are learning how other cell formatting options work, which means, no need to click [OK] at step 7 in this task. When applying conditional formatting, if you inadvertently click on [OK] instead of [Cancel] , you can either use the Clear Rules optio n or the Undo (ctrl+z) tool in the Quick Access Toolbar to remove the unwanted formatting. T OP T EN ITEMS Conditional formatting can be used in a worksheet to highlight upper and lower values. For example, it is often interesting to know your top 10 customers, or the top 10% of products sold in the last year. This can be achieved using the Top/Bottom Rules men u which can be accessed from the Conditional Formatting menu. TASK: 1. Select the range C4:H10 in the Used Vehicle Sales worksheet 2. Click on the Home tab, click on Conditional Formatting in the Styles group, then point to Top/Bottom Rules to see the available options 3. Select Top 10 Items to display the Top 10 Items dialog box – shading will now be applied to the top 10 items in the range 4. Click on the down spinner arrow next to the quantity until 5 appears, as shown. The top 5 items in the range will now be coloured… 5. Click on the drop arrow for with , then select Yellow Fill with Dark Yellow Text to change the colour of the shading 6. Click on [OK] to apply the formatting 7. Click outside the range to deselect it and see the results more clearly Note: Do not confuse the Top 10 Items with the Top 10% – the former displays only 10 results while the latter can display a variable amount of results based on what fits into the top ten percent category. M ORE T OP A ND B OTTOM F ORMATTING O PTIONS The Top/Bottom Rules option in the Conditional Formatting menu provides a number of useful options for displaying upper and lower ranges in your data. You can display the top and bottom number of values, the top and bottom percentage, and whether values are above or below average. TASK: 1. Select the range C4:H10 in the Used Vehicle Sales worksheet . This includes all of the sales for BMW motor vehicles… 2. Click on the Home tab, click on Conditional Formatting in the Styles group, point to Top Rules , then select Top 10% to display the Top 10% dialog box 3. Spend a few moments studying the results 4. Click on the drop arrow for with , then select Green Fill with Dark Green Text to change the colour of the shading 5. Click on [OK] to apply the formatting Note: The up and down spinner arrows next to the values in the conditional formatting dialog boxes allow you to refine your conditional formatting for the number of items and the percentage of items. W ORKING W ITH D ATA B ARS It is sometimes difficult to spot pat terns or trends when confronted with a worksheet full of figures. Conditional formatting allows you to colour cells so that you can see how the figures move from high value to low value. Data bars provide colour accents to cells in the selected range. The width of the accents depends on the data value and its size relative to the overall total value. TASK: 1. Select the range C11: I11 in the BMW worksheet . This range represents the total monthly sales of BMW vehicles… 2. Click on the Home tab, click on Conditional Formatting in the Styles group, then point to Data Bars to see the available options 3. Point to the options to see a Live Preview of the result 4. Click on Orange Data Bar under Gradient Fill , then click outside the range to deselect it. The bars indicate the size of each value relative to the total Note: To change to a different Data Bar colour, select the required range, click on the Home tab, then click on Conditional Formatting in the Styles group, point to Data Bars and click on an option to apply it. W ORKING W ITH C OLOUR S CALES Using colour scales , you can highlight the values of cells in a selected range relative to the total value of all cells in that range. After selecting a colour scale, each cell in the range is shaded with a different hue and intensity of one of the colours in the scale, depe ndent upon its value relative to the overall selection total. TASK: 1. Select the range C4:H4 in the BMW worksheet .This is the vehicle monthly sales for the 3 rd Series BMW vehicles… 2. Click on the Home tab, click on Conditional Formatting , then point to Colour Scales to see the options available 3. Point to the colours to see a Live Preview of the result how coloured shading appears across the selected range 4. Select Green -Yellow – Red Colour Scale 5. Select the range C7:H8 in the BMW worksheet . This is the vehicle monthly sales for the X Series BMW… 6. Repeat steps 2 and 4 to apply Green -Yellow – Red Colour Scale shading 7. Click outside the range to deselect it and see the results Note: With a three colour option, Excel divides the values accordi ng to the number of cells in the range and then applies a scale of hues. In the Green -Yellow -Red Colour Scale option the first colour ( Green ) is applied to the highest value, while the last colour ( Red ) is applied to the lowest. W ORKING W ITH ICON S ETS Using colours to conditionally format ranges in a worksheet is fine providing that your readers are capable of interpreting and indeed even seeing colours. In lieu of colouring a conditionally formatted range, you can apply icons to the range. With icons a symbol is placed next to a cell to indicate the position of that value relative to the range total. TASK: 1. Select the range I4:I10 in the BMW worksheet . This is the total sales for each BMW model over six months… 2. Click on the Home tab, click on Conditional Formatting in the Styles group, then point to Icon Sets to see a gallery of options 3. Point to the options to see a Live Preview of the result in the spreadsheet 4. Under Indicators , click on 3 Symbols (Uncircled) to select this opti on and apply it to the selected range. The hash (#) symbols next to the icons show the column is too narrow to display the values… 5. In the column header area, double -click on the right margin of column I to widen it to fit the content 6. Click outside the range to view the result Note: Using the 3 Symbols (Uncircled) conditional formatting option, there are three icons that are applied according to the rule of thirds in the range. Values that fall within the top third receive the tick icon, values in the s econd third receive the exclamation mark, and the values in the last third receive the cross. TASK: 1. Select the range B3:J17 in the Class worksheet . 2. Assume you’re the teacher and going to apply conditional formatting to easily check all of the grades that are below passing. Apply Conditional Formatting so it will highlight cells containing values less than 60 with only light red fill. 3. In addition, you would like to do quick check how the grades compare to each other. T hat’s why decided to apply the Icon Set called 3 Symbols (Circled). Hint : The names of the icon sets will appear when you hover over them. U NDERSTANDING S PARKLINES Sparklines are mini -charts that are inserted into a single cell of a worksheet. You use Sparklines to graphically represent trends and patterns in the data in a specific range of cells in the worksheet. You insert Sparklines from the Insert tab on the ribbon. What A re Sparklines? Sparklines are simply mini -charts embedded into a single cell. Before you insert a Sparkline you must select the range of values you want the Sparkline to represent. Each of the Sparklines above charts the figures for the Jan , Feb and Mar columns in the row to their left. For example, the Sparkline in cell G4 charts the figures in the range C4:E4 . There are three different types of Sparklines available in Excel: Line , Column and Win/Loss , as shown above. The Line Sparkline displays as a line chart. When dots appear in conjunction with the lines it is because the Sparklines have been asked to display the highest and lowest values and the dots represent these values. A Column Sparkline displays as vertical bars. The Win/Loss Sparkline displays positive values in one colour above an imaginary line and negative values in another colour below that imaginary line. While Sparklines are not as versatile as charts, you still have a lot of form atting options at your disposal when working with them. When a cell containing a Sparkline is selected, the Sparkline Tools: Design tab will appear in the ribbon as shown below. The Sparkline Tools: Design tab The Sparkline Tools: Design tab allows you t o change the type of Sparkline in a cell, change formatting options such as style and colouring, and show various high points, low points and the like. There is also a command that allows you to clear Sparklines from the cell. C REATING S PARKLINES You c an choose to create a Line , Column or Win/Loss Sparkline , depending on the type of data you are working with. While it is more efficient to get it right at this stage, you do have the option of changing the type of Sparkline after it is created. Like other forms of charting you will need to select the data series before creating the Sparkline. TASK: 1. Select the range C4:E 5 in the Ford worksheet 2. Click on the Insert tab, then click on Column in the Sparklines group to display the Create Sparklines dialog box. The dialog box shows the data range but requires a location for the Sparkline… 3. Click in cell G4 :G5 in the Ford worksheet, then click on [OK] to create the Sparkline 4. Select the range C6:E7 5. On the Insert tab, click on Line in the Sparklines group to display the Create Sparklines dialog box 6. Click in cell G6:G7 in the Ford worksheet, then click on [OK] 7. Repeat the above steps to create a Win/Loss Sparkline in G8:G9 for the range C8:E9 Note: You may need to increase the row height of rows which have Sparklines in them in order to see the trends clearly. E DITING S PARKLINES Sparklines in Excel are both easy to create and easy to edit. When a cell containing a Sparkline is selected, the Sparkline Tools: Design tab will appear on the ribbon. This tab allows you to change the type of Sparkline in the cell, its formatting and col ouring, and to specify things such as high and low points. You can also fill Sparklines to adjacent cells. TASK: 1. Click in cell G4 in the Ford worksheet , then click on the Sparkline Tools: Design tab 2. Click on the Sparkline Colour drop arrow in the Style group, then click on Green, Accent 6 to apply this colour to the Sparkline 3. In the Show group, click on High Point so it is ticked. The highest value in the Sparkline will appear in a different colour… Note: You can edit multiple Sparklines by selecting the cells in which they are located as a range. You can place a Sparkline in a cell containing data – the data and the Sparkline will simply appear together.