Chapter 11 of the advanced Excel unit goes over how to use Autofilter and advanced filter. They both can be found in data then filter. When you click on autofilter these little arrows are put in each of the headings and when clicked on you can find all of the data that fits the criteria. Such as if you had a column for cost, you would be able to find data that has a certain cost or the top 10 of the list for cost. Also you are able to sort ascending and descending, which is very helpful. The advanced filter is a way to find data in your list and to copy it to another location. Activity 11.1 talks about it more in depth.
The first project asks you to use both autofilter and advanced filter to find information. It says, using autofilter to find products that cost less than $1.00 and print out that page. Next it says to use an advanced filter to find records with a quantity in stock that is less than 100. In activity 11.1 I was asked to do the same thing except with different numbers. So I followed those same steps and put in <100>$5,000.
The final project asks you just to use advanced filter to find products that are available in a small size. Then it asks you to print it in portrait orientation. Then it also asks you to find large sizes that have a sale price greater than $10.00. So that means that the advanced filter will be filtering on two criteria
Friday, December 12, 2008
Thursday, December 4, 2008
Ch.10--"Creating and Editing Lists in Excel"
Chapter 10 involves creating and editing lists. It also shows how to add records to a data list. You can go to data form from under the data tab and typing in new data becomes easier. Editing records is also very simple; you can either use data form or just simply change the cell right on the sheet. You can delete records in a list along with the rest of this. Again you can use data form and delete the information you don’t wish to have. To sort a list go to data and choose sort then sort however you wish.
The first project is very simple. You sort the list by division, then by last name, and then by first name. I did this by going to data then sort. The second project asks you to enter in the data that they show. It says to make it easier you can format the cells as you wish. Then I sorted the data by state then city in order to quickly glance at the subcontractors available in a specific area.
The activity that I had to do was 10.1 and it had a table of information for you to copy in a worksheet in excel. To increase the table’s appearance I applied an autoformat to it and sorted the list in ascending order by Item No. For the cost and selling price columns I formatted them to currency with two decimals.
The first project is very simple. You sort the list by division, then by last name, and then by first name. I did this by going to data then sort. The second project asks you to enter in the data that they show. It says to make it easier you can format the cells as you wish. Then I sorted the data by state then city in order to quickly glance at the subcontractors available in a specific area.
The activity that I had to do was 10.1 and it had a table of information for you to copy in a worksheet in excel. To increase the table’s appearance I applied an autoformat to it and sorted the list in ascending order by Item No. For the cost and selling price columns I formatted them to currency with two decimals.
Tuesday, December 2, 2008
Ch.9--"Transposing text and modifying charts in Excel"
Excel chapter 9 talks about how to use auto format, conditional format, enhance appearance of charts and transposing text. Auto format is under the format tab and it formats data that you selected and puts it into a table format that enhances the data’s presentation. Conditional format will highlight any number that you say is either between two numbers, greater than a number or less than a number. It is very useful for identifying numbers that are different from the rest. To enhance the appearance of charts you open the chart toolbar and go to format chart area. You can do things like make its’ corners round or create a shadow effect. There may be times when data is entered in the worksheet in columns or rows and then you realize it would be more appealing if you switched them. That’s what transposing is. You highlight your columns or rows then go to Edit then Paste Special and choose transpose.
The first project makes you format a range of cells and change the date format. (Something that I learned in introductory Excel) Then it asks to take range of cells C4:G14 and paste them in the second sheet. To print the worksheet it says to print it in landscape, which is easily done by going to file then page setup.
The second project asks you to format the sales figures with a $ sign and two decimal places. So I highlighted those cells and went to format cells then number then finally currency with two decimal places. In that same format cells box is a heading that says alignment- that is where I found to make the headings to 45 degrees. The book tells you to merge and center, the title and subtitles only, columns A through D. Then finally I transposed the data in A4:D11 by going to edit then paste special.
The third project has you play around with a chart. It tells you to add a data table to it, to resize it and to change the border style of the chart to rounded edges. To add the data table just open the chart toolbar and choose add data table. To resize it drag one of its’ corners and to change the border style go to format chart area in the chart toolbar and choose rounded corners.
The activity that I had to do was to apply an autoformat to the projected sales data and use conditional formatting to highlight the sales figures that exceed $60,000 for any month and any division. Both of these things can be done by highlighting the cells A4-M12 and go to format and choose autoformat – choose one – then go to conditional formatting in the same heading. Put in cell value is greater than 60,000.
The first project makes you format a range of cells and change the date format. (Something that I learned in introductory Excel) Then it asks to take range of cells C4:G14 and paste them in the second sheet. To print the worksheet it says to print it in landscape, which is easily done by going to file then page setup.
The second project asks you to format the sales figures with a $ sign and two decimal places. So I highlighted those cells and went to format cells then number then finally currency with two decimal places. In that same format cells box is a heading that says alignment- that is where I found to make the headings to 45 degrees. The book tells you to merge and center, the title and subtitles only, columns A through D. Then finally I transposed the data in A4:D11 by going to edit then paste special.
The third project has you play around with a chart. It tells you to add a data table to it, to resize it and to change the border style of the chart to rounded edges. To add the data table just open the chart toolbar and choose add data table. To resize it drag one of its’ corners and to change the border style go to format chart area in the chart toolbar and choose rounded corners.
The activity that I had to do was to apply an autoformat to the projected sales data and use conditional formatting to highlight the sales figures that exceed $60,000 for any month and any division. Both of these things can be done by highlighting the cells A4-M12 and go to format and choose autoformat – choose one – then go to conditional formatting in the same heading. Put in cell value is greater than 60,000.
Subscribe to:
Posts (Atom)