Practical Application: Creating Worksheets & Workbooks in Excel

Instructor: Beth Hendricks

Beth holds a master's degree in integrated marketing communications, and has worked in journalism and marketing throughout her career.

There's more to creating a worksheet than filling data into cells. In this activity, you'll read through several Excel-based scenarios to test your knowledge of importing, opening, adding, moving and copying.

Working in Excel

You may think of Excel as a mathematician's or accountant's tool, but you couldn't be more wrong! This spreadsheet program has a lot of flexibility from making charts to storing important information ... IF you know your stuff.

You learned a lot about working with Excel in the lesson: Excel: Skills Development & Training. In this practical activity, you're going to see how well you can implement what you learned... and maybe learn a few new tricks along the way.

In the next section, take time to read through the two presented scenarios. When you get to the analysis questions, pause and think about (or even write down!) your response to each. Continue on and compare your answers with the guidance given. Next, step away from your web browser... and try your hand at replicating the skill portrayed!

Excel Scenarios

Scenario 1: Importing and Opening Files

Jordyn and Tessa have been working on data for their company's mid-year market report. Tessa has gathered all of the numbers and entered them into a spreadsheet to make it simpler for Jordyn to extract the data and lay out the report. As she is going through the numbers, however, Jordyn notices what appears to be an error in the data.

She extracts the worksheet in question from its Excel file, and drops it into a shared file system that both she and Tessa have access to. Soon after, Tessa gets a text from Jordyn about the numbers in question, and equipped with a new sense of purpose, she prepares to dive back into the file. But what should she do next?

Ask Yourself:

  1. What are Tessa's options for opening the worksheet sent back to her by Jordyn?
  2. Where can Tessa find the option to open the file if Excel is already open?

Possible Solution:

Tessa decides to open Excel first since she knows that the shared file is on the company drive. She could, of course, navigate to the drive and double-click the file, but decides against it because it might be easier to open Excel first and have more control over how she opens the file.

When she opens Excel, she is typically prompted to open one of Excel's templates. Instead, she should navigate to the left bar and click ''Open Other Workbooks.'' This will enable her to see an option to open a shared file (the option usually labeled ''Shared with Me''). From there, she can find the shared file and open it easily. The other options here are to create a new file or to open a recent file.

Setback:

Uh-oh! Apparently Jordyn did not prepare the file correctly. When Tessa tries to open it, it does not seem to be the right file type she is used to seeing when opening an Excel file. Now what?

Ask Yourself:

  1. How can Tessa open the file from Jordyn?
  2. Is there more than one way to open an Excel file with an unusual file extension?
  3. What is the first step Tessa must take to import the different file?

Possible Solution:

Tessa suddenly realizes that Jordyn exported the file using CSV format. Although Excel can read it fine, the file extension is different and it may not show up (or load) when she chooses it. Most Excel files have an XLS or XLSX file extension. But this file has a CSV extension. She learns that CSV stands for comma separated values, which simply means that Jordyn saved it as a text file by mistake, and each piece of information is separated by a comma (or any delineator really, like a tab character or linefeed). Another difference is that CSV files lack the nice formatting of an XLS or XLSX file, but they are readable by almost any program, too.

So, one way Tessa can load the CSV file from Jordan, is to use the file filter dropdown box when she is opening the file from Jordyn. This dropdown is next to the file name in the open file dialog. She can change it from ''All Excel Files'' to ''Text Files (*.prn;*.txt*;*.csv)''. Now when she browses for Jordyn's file, she will be able to spot the CSV file and load it like any other Excel data file. She will be prompted for which delineator was used (a comma, a tab, or similar), but she finds the prompts very easy to understand, and the import dialog lets her preview the result, so she can tell she is importing the CSV file correctly.

Alternatively, Tessa could use the import function to load the CSV file. Her first step here is to open a new blank workbook. From there, she can click ''Data Menu'' and choose the option, ''From Text/CSV.'' Following Excel's prompts during the import process, Tessa can generate a correctly formatted Excel file she can work with.

It's Your Turn:

Now, it's your turn. Find a free sample spreadsheet by doing an Internet search for one (there's lots available from different websites) or find one already on your computer.

  • Open Excel and navigate to ''Open Other Workbooks''
  • Next, you'll choose to ''Open a Recent File'', where you'll locate the test spreadsheet on your computer
  • If you want to test your hand at importing CSV files, you can also find samples on the Internet. Locate one and open a new workbook in Excel
  • Use either of the two methods you just learned to import the CSV file. Follow the prompts during the import process and you'll turn that data into a usable Excel file in no time

Scenario 2: Adding, Moving and Copying Worksheets

Carrie has decided to keep track of her monthly expenses using an Excel spreadsheet, but she has way too much information to fit in a single worksheet. She wants the flexibility to split that information into months, and to shuffle those pieces around as she prepares for the next calendar year. What information does Carrie need to handle these tasks proficiently?

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support