Scribd
Upload
76 views19 pages

Spreadsheet Basics and Exercises Guide

The document contains instructions for 7 exercises to practice basic spreadsheet concepts. The exercises involve typing data into spreadsheets, formatting numbers and text, creating formulas to calculate totals and sums, and applying borders and shading. The goal is to recreate sample spreadsheet outputs that are not shown. Each exercise contains similar instructions regarding data entry, formatting, formulas, and styling.

Uploaded by

Araiza Flores
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
76 views19 pages

Spreadsheet Basics and Exercises Guide

The document contains instructions for 7 exercises to practice basic spreadsheet concepts. The exercises involve typing data into spreadsheets, formatting numbers and text, creating formulas to calculate totals and sums, and applying borders and shading. The goal is to recreate sample spreadsheet outputs that are not shown. Each exercise contains similar instructions regarding data entry, formatting, formulas, and styling.

Uploaded by

Araiza Flores
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Basic Spreadsheet Concepts Exercise 1

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.


2) Format all numbers as a currency.
3) Center the spreadsheet heading 'Sales for the Month' across the spreadsheet.
4) Format all text as displayed in the sample below.
5) Create formula's to display a total for each sales rep.
6) Create formula's to display a total for each product.
7) Create a formula to calculate the total sales for all sales rep's for the month.

Basic Spreadsheet Concepts Exercise 2

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.


2) Format all numbers as a currency.
3) Center the spreadsheet heading 'Sales And Produce Department' across the spreadsheet.
4) Format all text as displayed in the sample below.
5) Create formula's to display a total for each fruit.
6) Create formula's to display a total for each month.
7) Create a formula to calculate the total sales for all fruit items for the year to date

.
Basic Spreadsheet Concepts Exercise 3

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.

2) Format all numbers with appropriate format's.

3) Center the spreadsheet heading 'Mike's Stationery Order' across the spreadsheet.

4) Format all text as displayed in the sample below, including the rotated text labels.

5) Create formula's to display a total for each stationery item ordered.

6) Create a formula to calculate the total for the stationery order.


Basic Spreadsheet Concepts Exercise 4

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.

2) Format all numbers with appropriate format's).

3) Center the spreadsheet heading 'Travel Expenses For The Month Of May' across the spreadsheet.

4) Format all text as displayed in the sample below, including the rotated text labels.

5) Create formula's to display a total for each Sales Rep.

6) Create formula's to calculate the amount due to each sales rep, on the basis that each rep is
reimbursed 0.63 cents per kilometer traveled.

7) Apply all borders and shading (colour) shown in the sample below, feel free to experiment with
your own colour schemes.
Basic Spreadsheet Concepts Exercise 5

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.

2) Format all numbers with appropriate format's).

3) Center the spreadsheet heading "Mike's Lunch Bar" across the spreadsheet.

4) Format all text as displayed in the sample below.

5) Create formula's to display a total for each item in the Lunch Bar.

6) Create a formula to calculate the total sales for the Lunch Bar.

7) Apply all borders and shading (colour) shown in the sample below, feel free to experiment with
your own colour schemes.
Basic Spreadsheet Concepts Exercise 6

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.

2) Format all numbers with appropriate format's).

3) Center the spreadsheet heading "Mike's Furniture Store" across the spreadsheet.

4) Format all text as displayed in the sample below.

5) Create formula's to display a total for each item in the Lunch Bar.

6) Create a formula to calculate the total sales for the Lunch Bar.

7) Apply all borders and shading (colour) shown in the sample below, feel free to experiment with
your own colour schemes.
Basic Spreadsheet Concepts Exercise 7

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below.

2) Format all numbers with appropriate format's).

3) Center the spreadsheet heading "Weekly Income Schedule" across the spreadsheet.

4) Format all text as displayed in the sample below.

5) Create formula's to display a total for each item in the Lunch Bar.

6) Create formula's to calculate the Total Exp (Total Expenditure).

7) Create formula's to calculate the profit.

8) create formula's to calculate the totals for each column.

9) Apply all borders and shading (colour) shown in the sample below, feel free to experiment with
your own colour schemes.
Intermediate Spreadsheet Concepts Exercise 1

Create a spreadsheet using the following information. You are the owner of a fruit store, you have
owned the fruit store for one complete year. Use the data below to construct a spreadsheet to display
the sales figures for the first year of operation of your fruit store.

1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font
Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate totals for each month.
8) Create formula's to calculate totals for each fruit item.
9) Setup an appropriate header for this spreadsheet.
10) Setup a page number for this spreadsheet and place it in the footer.

Apples Bananas
January 2298 January 6899.21
February 3512.56 February 6755.33
March 4929.67 March 6541
April 5883 April 6032.79
May 6237.77 May 5822.72
June 6566.78 June 5968
July 6213.88 July 6333.33
August 6001 August 6544.11
September 5799.69 September 6845.45
October 5527 October 7000.01
November 3914.55 November 7216.27
December 2564.99 December 7283

Oranges Kiwifruit
January 4923.88 January 3349.12
February 4444.99 February 3899.87
March 3851 March 4336
April 3399.88 April 4892.01
May 3020.03 May 5217.97
June 3411.89 June 4764.10
July 3567.09 July 4523.22
August 3999.91 August 4501
September 4255.88 September 4219.91
October 4873 October 3877.49
November 5214.95 November 3712.12
December 5521.17 December 3555.56

Pears Grapes
January 3310 January 4847.23
February 4524.65 February 5361.58
March 5992.76 March 5899.24
April 6961.44 April 6853.01
May 7447 May 5471.34
June 7583.87 June 4534.22
July 7393 July 4222.83
August 7110.10 August 4137
September 6637.96 September 3998
October 6275 October 3556.36
November 4841.71 November 3111.44
December 3456.11 December 2789.74

Peaches Nectarines
January 5902.44 January 5310
February 7234.12 February 7772.71
March 5110 March 8219
April 3521.87 April 6989.33
May 1276.34 May 4535.52
June 1227.30 June 1873.38
July 1199.99 July 1198
August 1242.09 August 1241.63
September 1189.73 September 1187.57
October 1195.42 October 1222.21
November 1213.14 November 1432.43
December 1887.49 December 2137.78
Intermediate Spreadsheet Concepts Exercise 2

Create a spreadsheet using the following information. You have been tasked with creating a
spreadsheet to generate and stationery order for the month of March.

1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font
Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate totals for each stationery item.
8) Create a formula to calculate the order total for the month.
9) Create a formula to calculate the G.S.T or V.A.T component of the order. Note all prices
include G.S.T or [Link]., and the value of G.S.T or V.A.T is 12.5% or divide by 9.
10) Create a formula to calculate the net total (pre G.S.T or V.A.T total) of the order for the month.
11) Setup an appropriate header for this spreadsheet.
12) Setup a page number for this spreadsheet and place it in the footer.

Description Quantity Cost per item


Note Pad 202 2.85
Highlighter Pen 280 2.95
Ball Point Pen Blue (pkt) 520 2.50
Ball Point Pen Red (pkt) 340 2.50
Ball Point Pen Green (pkt) 250 2.50
Exercise Book 1B 35 4.95
Cello-tape 75 2.95
Manila Folders 100 2.95
A4 Refill Pad 90 5.95
Writing Pad 86 3.95
Pencil Sharpeners 110 0.95
Crayons (pkt) 80 3.85
Pencils 603 0.5
Colour Pencils (pkt) 50 2.95
Felt Pens (pkt) 40 3.95
Staples (pkt) 75 3.50
Stapler 30 9.95
Hole Punch 25 14.95
Ring Binder 45 10.95
Intermediate Spreadsheet Concepts Exercise 3

Create a spreadsheet using the following information. You have been asked to prepare a
spreadsheet using the results of a survey on the types of fizzy drinks people prefer. 3000 people
where asked what their favorite fizzy drink is, and what their next preference would be if their favorite
was not available.

1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font
Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate the percentage of the total number of people surveyed that
preferred a specific fizzy drink as their first preference.
8) Create formula's to calculate the percentage of the total number of people surveyed that
preferred a specific fizzy drink as their second preference.
9) Setup an appropriate header for this spreadsheet.
10) Setup a page number for this spreadsheet and place it in the footer.

Drink 1st Preference 2nd Preference


Pepsi 342 403
Coca Cola 359 367
7Up 238 290
Lift 215 190
Mountain Dew 321 311
Fanta 103 80
Ginger Beer 180 380
Leed Lemonade 217 120
Lemonade 215 105
L&P 425 414
Vanilla Coke 203 67
Don't Knows 182 273
Intermediate Spreadsheet Concepts Exercise 4

Create a spreadsheet using the following information. You have been asked to prepare a
spreadsheet to show the profit and loss figure for the last financial year. The profit and loss should be
shown as a dollars and as a percentage.

1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font
Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate the profit / loss as a currency for each month.
8) Create formula's to calculate the profit / loss as a percentage for each month.
9) Create formula's to calculate the Annual Totals for the Income, expenditure, and profit / loss.
10) Create a formula to calculate the profit / loss for the whole year.
11) Setup an appropriate header for this spreadsheet.
12) Setup a page number for this spreadsheet and place it in the footer.

Month Income Expenditure


March 1259.9 1410.45
April 1163.98 1499.10
May 1533 1370.25
June 1774 1440.8
July 1631 1530.25
August 1658 1490.55
September 1781 1369
October 1821.54 1420
November 2233.82 1611.81
December 2537.22 1577.63
January 1650 1423.98
February 1623 1598.12
Intermediate Spreadsheet Concepts Exercise 5

Create a spreadsheet using the following information. You have been asked to construct a
spreadsheet for Mike's Mail Order Company.

1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font
Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate delivered cost for each item.
8) Create formula's to calculate the Monthly Income for each Item, the Monthly Income from the
delivery of each Item, and the total Monthly Income for each item.
9) Create formula's to calculate totals for each column in your spreadsheet.
10) Set the layout of your spreadsheet to display the header or column labels at the top of each
page when printed.
11) Setup an appropriate header for this spreadsheet.
12) Setup a page number for this spreadsheet and place it in the footer.

Item Price Delivery Ordered


Charge This
Month
Bath Towels $6.95 $5.00 319
Bathroom Radio $24.95 $8.00 150
Bathroom Scales $99.95 $10.00 63
BBQ $199.95 $20.00 38
Beach Towels $9.95 $5.00 412
Board Games $39.95 $8.00 95
Book Ends $29.95 $10.00 241
Book Shelf $49.95 $15.00 168
Camera $69.95 $10.00 112
Card Games $14.95 $5.00 243
Carving Fork $17.95 $5.00 177
Clothes Hanger $4.95 $5.00 588
Computer Games $89.95 $5.00 126
Cooking Timer $21.95 $5.00 169
Cordless Telephone $119.95 $10.00 87
Curtain Rails $129.95 $10.00 94
Curtains $124.95 $8.00 317
Digital Camera $999.95 $10.00 349
Digital Clock $99.95 $10.00 476
Double Sheets $49.95 $5.00 355
Electric Blender $299.95 $20.00 122
Electric Knife $149.95 $10.00 52
Electric Mixer $399.95 $20.00 12
Fan Cooker $199.95 $20.00 49
Fire Extinguishers $249.95 $20.00 79
Hand Towels $7.95 $5.00 513
King Sheets $69.95 $5.00 299
Kitchen Scales $89.95 $15.00 83
Kitchen Utensils $29.95 $10.00 197
Microwave Oven $799.95 $20.00 42
Model Boat $59.95 $10.00 179
Model Plane $99.95 $10.00 134
Model Yacht $64.95 $10.00 187
Night Light $29.95 $8.00 262
Pillow Slips $7.95 $5.00 578
Pillows $19.95 $10.00 345
Plant Pots $9.95 $5.00 899
Queen Sheets $59.95 $5.00 444
Rotary Oven $249.95 $20.00 68
Security Lights $64.95 $10.00 81
Serving Spoon $9.95 $5.00 164
Single Sheets $29.95 $5.00 475
Smoke Detectors $19.95 $5.00 687
Stop Watch $34.95 $5.00 265
Tea Towels $18.95 $5.00 947
Tennis Balls $8.95 $5.00 1,421
Wall Clock $49.95 $10.00 186
Wine Opener $29.95 $5.00 197
Wine Racks $79.95 $20.00 59
Fruit Bowl $29.95 $5.00 799
Carving Knife $19.95 $5.00 1,254
Tooth Picks $3.95 $5.00 2,233
Screwdriver Set $19.95 $5.00 588
Drill Set (10 Piece) $24.95 $5.00 472
Electric Tooth Brush $23.95 $5.00 991
Coffee Cup Set $14.95 $10.00 631
Tea Set $39.95 $10.00 481
Lipstick Selection $21.95 $5.00 999
Quick Burner $19.95 $5.00 1,087
BBQ Lighters $9.95 $5.00 2,378
Cup Hooks $2.95 $5.00 2,594
Table Tennis Bats $10.95 $5.00 276
Table Tennis Balls $1.95 $5.00 552
Table Mats $12.95 $10.00 773
Chopping Board $14.95 $15.00 182
House Alarm System $199.95 $20.00 95
Massage Pillow $49.95 $10.00 423
Christmas Tree Lights $29.95 $10.00 432
Christmas Decorations $24.95 $10.00 587
Dinner Set $38.95 $20.00 333
Potato Masher $9.95 $5.00 199
Potato Peeler $7.95 $5.00 156
Potato Eye Remover $5.95 $5.00 123
Apple Core Remover $5.95 $5.00 138
Electric Toaster $99.95 $15.00 185
Camping Oven $149.95 $20.00 50
Tent (2 person) $189.95 $20.00 42
Tent (4 person) $249.95 $20.00 68
Basic Chart Concepts Exercise 1

Use the Fruit Sales spreadsheet created in the Intermediate Spreadsheet Exercise 1, to create the
Pie Chart below.

Create similar Pie Charts for the other Fruits in this Spreadsheet. Hint: use the CTRL key to highlight
or select the cells or columns you need to create these charts.

Basic Chart Concepts Exercise 2

Use the Fruit Sales spreadsheet created in the Intermediate Spreadsheet Exercise 1, to create the
Pie Chart below.
Create similar Pie Charts for the other Months in this Spreadsheet. Hint: use the CTRL key to
highlight or select the cells or rows you need to create these charts.

Basic Chart Concepts Exercise 3

Use the Fruit Sales spreadsheet created in the Intermediate Spreadsheet Exercise 1, to create the
Pie Chart below.
Basic Chart Concepts Exercise 4

Use the Fruit Sales spreadsheet created in the Intermediate Spreadsheet Exercise 1, to create the
Pie Chart below.

Basic Chart Concepts Exercise 5

Use the Fruit Sales spreadsheet created in the Intermediate Spreadsheet Exercise 1, to create the
Line Chart below.
Basic Chart Concepts Exercise 6

Use the Fizzy Drink Survey spreadsheet created in the Intermediate Spreadsheet Exercise 3, to
create the Pie Chart below.
Basic Chart Concepts Exercise 7

Use the Fizzy Drink Survey spreadsheet created in the Intermediate Spreadsheet Exercise 3, to
create the Pie Chart below.

Basic Chart Concepts Exercise 8

Use the Stationary Order For The Month Of March spreadsheet created in the Intermediate
Spreadsheet Exercise 2, to create the Pie Chart below.

Common questions

Powered by AI

Formulas in spreadsheets allow for dynamic calculations by automatically updating totals when source data changes, providing real-time data analysis. For instance, creating formulas to display totals for sales reps or products enables users to quickly assess performance without manual recalculations . Moreover, by calculating percentages of survey preferences or monthly income from sales, users can identify trends and make informed decisions . Formulas facilitate complex data analysis by linking and processing information across different parts of the spreadsheet or from various datasets, offering comprehensive insights.

Centering spreadsheet headings and applying consistent formatting create a structured and professional appearance, improving user comprehension and page aesthetics. For example, centering 'Sales And Produce Department' and consistently formatting text elements across a spreadsheet ensure that each element is visually aligned and easily readable . This uniformity helps avoid confusion and directs the viewer's attention appropriately, facilitating better understanding of the spreadsheet's contents.

Formatting text and numbers in a spreadsheet enhances readability by making key elements stand out and ensures data is presented in a clear and consistent manner. For example, setting all numbers as currency in financial reports provides a universal understanding of values, and centering headings like 'Sales for the Month' across columns creates a clear visual hierarchy . Consistent formatting helps users to quickly grasp information, enhancing both visual appeal and functionality. It aids in distinguishing between different types of data, such as text labels and numerical calculations, and can highlight important areas needing attention.

Applying borders and shading in spreadsheets improves data presentation by enhancing readability and drawing attention to specific areas. Borders can frame data sets, making them easily discernible, while shading can highlight important sections or differentiate between grouped data . These visual cues guide user interaction by focusing viewer attention on critical data points and facilitating easier navigation through complex datasets. Aesthetic enhancements make the information visually appealing and can convey emphasis without altering core data.

Headers and footers play a crucial role in organizing data by providing consistent reference points for readers and enhancing navigability. Adding a title or page number as a header/footer ensures users can easily identify the content and navigate through documents, especially in larger spreadsheets . They facilitate cross-referencing by keeping key information available on every page, improving the user experience during data handling and review. Setting up headers with identifiers such as page numbers or titles simplifies document tracking and access.

Using pie charts for visualizing survey data in spreadsheets allows for quick, intuitive understanding of proportional relationships within a dataset. Pie charts, as created from the Fizzy Drink Survey data, effectively display each drink's popularity, making it easy to compare preferences at a glance . They highlight how individual components contribute to a whole, facilitating immediate comprehension among stakeholders. This visual representation aids in communicating findings to audiences that may lack sophisticated data interpretation skills and can prompt strategic adjustments based on visual trends.

Adjusting column width and row height enhances the usability of a spreadsheet by ensuring that data is not truncated and is easily viewable, optimizing space utilization. Ideal adjustments can make complex data more accessible and prevent visual clutter by aligning all information neatly . This optimizes the viewing area, allowing for clearer presentation of data, whether it involves extensive texts or detailed numerical information, and improves the overall readability.

Creating formulas to total sales by product and monthly figures is crucial for in-depth retail analysis as it provides swift access to key performance metrics. These calculations allow retailers to track product performance over time, identify demand trends, and allocate resources efficiently . Monthly sales totals aid in understanding seasonality and inventory management, while product sales totals indicate profitability and growth potential. This analytical capability supports strategic decision-making, fostering adaptable and data-driven business operations.

Creating formulas for calculating delivery costs in a mail order spreadsheet automates and speeds up the process of understanding operational costs, thus improving efficiency. By predefined formulas to calculate delivered cost for each item, businesses can predict the actual financial outflow per product sold without manual computation . This enables accurate pricing strategies and quick adjustment to supply chain changes, ultimately enhancing the responsiveness of business operations. Automated calculations can reduce errors and provide management with timely, precise data for decision-making.

Setting up formulas for calculating monthly income and expenditure helps streamline financial analysis by providing immediate insights into profit and loss trends. By calculating the monthly profit/loss as both currency and percentage, spreadsheets allow comparison of financial health over different periods . This enables businesses to detect patterns, adjust strategies promptly, and make data-driven decisions to enhance profitability. Real-time updates from formula-driven analysis ensure decisions are based on the most current data available.

You might also like