Open The WeAreVershoes Start File If The Workbook Opens ✓ Solved
Open The Wearevershoes 01xlsx Start File If The Workbook Opens In Pr
Open the WearEverShoes-01.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. Rename the file to include your name, save it, and change the project file name if instructed. Remove any split and freeze panes. Click the Split button on the View tab, Window group, and ensure it is removed. Freeze the column titles at cell A4 by selecting cell A4 and clicking Freeze Panes from the View tab. A border appears to indicate the frozen pane. Scroll down to row 31 and select cell E31 to ensure the pane is frozen at that point. Type "3" in cell E31 and press Enter. Return to cell A4 using Ctrl+Home. Change the zoom size to 100% and unfreeze panes by clicking Freeze Panes again. Select cell A1.
Check spelling in the worksheet, correcting "Hikinâ" to "Hiking" by selecting the word from the suggestions and clicking Change All, then OK. Use the Fill Handle to copy a formula from cell H4 downward to calculate stock values by multiplying quantity and cost. Return to cell A4 using Ctrl+Home. Change the theme to Ion from the Page Layout tab. Merge and center cells A1:J1 and A2:J2. Apply the Title style to cell A1 and Heading 4 to cell A2. Adjust font size to 14 pt for cell A2, and set row height of rows 1 and 2 to 22. Select cells A3:J3, apply Light Yellow style, and bold the text. Resize row 3 to a height of 21. Use the Status bar to zoom out for visibility of all rows.
Format cells by centering the data in G4:H39, applying Accounting Number format to G4:H39, and applying borders to A3:J39. Increase the indent in columns B4:C39 and A4:A39 for better readability. Use AutoFit to adjust column widths for columns A:J. Insert a new column B, label it "Disc?", and fill down with a pattern of "No", "No", "Yes". Increase indent in the pattern cells for clarity. Rename the worksheet tab to "Inventory".
Switch to Page Layout view to insert a footer, including the filename in the center. Return to Normal view and save the workbook. Change the page setup to Landscape orientation and fit content to one page wide and tall. Set the print area to cells A1:K11, preview the print, then return to the worksheet and save all changes. Finally, close the workbook.
Sample Paper For Above instruction
Sample Paper: Step-by-step Guide to Modifying the WearEverShoes Workbook
In this exercise, I was tasked with editing the Excel workbook "WearEverShoes-01.xlsx" by performing several modifications including formatting, freezing panes, correcting spelling, and setting up print options. Here is a detailed account of each step I undertook to fulfill these requirements efficiently and accurately.
Opening and Preparing the Workbook
The initial step was to locate and open the "WearEverShoes-01.xlsx" file. When the file opened in Protected View, I clicked "Enable Editing" to allow modifications. Recognizing that the filename included my name, I ensured it was correct or changed it as per instructor instructions, then saved the file. To facilitate better data viewing, I removed any existing split panes and froze the row and column titles at cell A4, using the "Freeze Panes" option from the View tab. This ensured that, when scrolling through the data, the titles remained visible. I confirmed the freeze by observing the solid border below row 3 that indicated the frozen pane.
Adjusting View and Correcting Data
I scrolled down to row 31 and selected cell E31 to verify the freeze was applied correctly. I input "3" into this cell and pressed Enter, which updated the data appropriately. Using Ctrl+Home, I returned to cell A4, then adjusted the zoom to 100% for clarity. To unfreeze panes, I clicked "Freeze Panes" again, which removed the previous freeze, and navigated back to cell A1 to reset the view.
Next, I checked the spelling throughout the worksheet. The spell check detected "Hikinâ" as misspelled, and from the suggestions, I selected "Hiking" to correct all instances—ensuring consistency and accuracy in terminology.
Calculating and Formatting Data
I used the Fill Handle feature to copy a formula from cell H4 downward through the column to automatically calculate stock values by multiplying quantities (column E) by unit costs (column G). Mouse actions involved double-clicking the Fill Handle to populate all relevant cells efficiently. I then changed the worksheet's theme to "Ion" via Page Layout, which provided a cohesive visual appearance.
Cell Merging and Styling
I merged and centered headers across A1:J1 and A2:J2, applying the "Title" cell style to A1 for prominence and "Heading 4" style to A2 for subheader clarity. The font size of the subheader was increased to 14 pt for emphasis, and row heights for rows 1 and 2 were set to 22 pixels for visual balance. Additionally, I styled row 3 cells with "Light Yellow, 40% - Accent3" and bolded this header row for differentiation. Adjusting the row height of row 3 to 21 pixels ensured neatness.
Cell Formatting and Data Alignment
I centered the data in columns G and H, which likely contained currency or financial data, applying the Accounting Number format for consistency. Borders were added around cells A3 through J39 to delineate data areas clearly. To improve readability, I increased indentation in columns B and C, which contained categorical labels, and used AutoFit to adjust all column widths for optimal display without unnecessary whitespace.
Inserting Data and Creating Patterns
Right-clicking column B, I inserted a new column and labeled the header "Disc?". I filled this column with a pattern: "No", "No", "Yes", sequentially, by selecting the pattern cells and double-clicking the Fill Handle, creating a repeating pattern. I increased indentation in these cells to visually separate the pattern from other data. Also, I renamed the worksheet tab to "Inventory" for clarity.
Page Layout and Printing
Switching to Page Layout view, I inserted a footer that displayed the filename centrally using the Header & Footer tools. Returning to Normal view, I adjusted the page setup: changed orientation to Landscape and scaled the content to fit on one page both vertically and horizontally. I defined the print area as A1:K11 to ensure only relevant data printed. Using the print preview feature, I reviewed the layout, then saved the work, solidifying all changes prior to closing the workbook.
Conclusion
This comprehensive process involved precise formatting, data management, and layout adjustments to prepare the workbook for presentation or printing. These steps enhance both visual appeal and clarity, essential for effective data analysis and reporting in Excel.
References
- Microsoft Support. (2023). Basic tasks in Excel. https://support.microsoft.com
- Excel Easy. (2023). Formulas and functions in Excel. https://www.excel-easy.com
- Contextures. (2023). Excel tips, tricks, and tutorials. https://www.contextures.com
- TechRepublic. (2022). Best practices for Excel formatting. https://www.techrepublic.com
- Excel Jet. (2023). How to use fill handle in Excel. https://exceljet.net
- GCF Global. (2023). Data formatting in Excel. https://edu.gcfglobal.org
- Ablebits. (2022). Advanced Excel tips. https://www.ablebits.com
- Chandoo.org. (2023). Excel dashboards and reporting. https://chandoo.org
- Smartsheet. (2023). Managing data in Excel. https://www.smartsheet.com
- Microsoft Docs. (2023). Customizing page setup options. https://docs.microsoft.com