2.1 Introduction to Microsoft Excel
2.1.1 Microsoft Excel Basics: Worksheet Layout
Slides: Excel Functions pptx
Microsoft Excel is the most commonly used spreadsheet application that displays and stores data in rows and columns. Spreadsheets allow us to easily arrange and manipulate data, especially numerical data.
In Excel, you start off by creating an Excel Workbook that can contain one or more worksheets. The layout of an Excel worksheet is pretty similar to Word. At the top, you can find the quick access toolbar and ribbon. The ribbon is made up of tabs that are divided into groups containing commands. At the bottom, you can find the sheets tab that you can use to add new or switch between worksheets in your workbook.
The work area of Excel, like all spreadsheet documents, is made up of intersecting rows and columns, creating cells. The cell name is made up of its column letter and row number (e.g. B3) and you can see the name of the selected cell in the name box. Next to the name box is the formula bar where you can input data and see the content of the selected cell.
Video: Basic Components of Excel
The following video will teach you about the basic layout of an excel worksheet. Becoming familiar with the excel workspace will be integral for your efficient use of the tools that a worksheet offers.
2.1.2 Data types and number formats
Data, the plural of datum, is a collection of information on certain subjects or items. Spreadsheets allow us to easily analyze and summarize the data that we have collected. In Forestry, we might want to make a spreadsheet containing data on trees such as identification number, species, crown class, diameter at breast height (cm), and tree height (m).
Inputting data in Excel can be done by entering them directly into a cell or by typing into the formula bar. There are 2 main types of data in Excel: Text and numeric. Text data are automatically left-aligned in cells, while numeric data are right-aligned.
Numbers can be displayed as percentages, dates, or currency by applying different number formats using the Format Cells function.
Video: Different Data Types and Number Formats
The following video describes the different data types and number formats in excel. Being able to properly apply functions or logic to your data in excel will rely on your ability to apply the correct number formats to the correct columns.
Here is the Excel file that was used in the video: Format Cells worksheet
Try to do the following things:
- Add a date format to the Hire Date column
- Add a currency symbol to the Salary column and remove decimals
Take a look at this excel fileNumbers formatted worksheet for an example of how you can do the above.
2.1.3 Importing other file types to Excel
Now that you have learned about the basics, the first step in working in Excel would be to add data that we can work with.
To add data into Excel, you can import other file types such as text (.txt) files or comma-separated values (.csv) files. To open these files in Excel, you will have to make sure that your file type is not “All Excel Files”, but “All Files”. This way, all types of files will be displayed in your folder. Excel will then show an Import Wizard that you can use to easily import the file into the program.
Video: How to Import Text file into Excel
The following video will describe how to import a text file into excel. This is an important procedure to be familiar with, as many datasets will be automatically downloaded as, or with, a text file that will require interpretation more easily done within excel.
Try to import a .txt file into an Excel workbook using this file example text file. The result can be seen in the video.