The following provides information and links to some of the questions that are frequently asked about Microsoft Excel at the Weigle Information Commons.
You can do this in several ways. First, you can hover the cursor over the line between two columns or rows until it turns into a black cross with arrows:
You can then click and drag your mouse until the row or column is the size you need. You can also right-click on the row number or column letter and choose “row height” or “column width” and enter in a value manually. Finally you can also use “autofit” to automatically change the row height or column width to fit your data. This is located on the Home tab, Cells group, under “format”. More information can be found here.
The easiest way to do this is to highlight the rows or columns that you want to hide, then right-click and select “hide”. To show the hidden rows or columns, highlight around the hidden rows or columns, then right-click and select “unhide”.
Under the View tab, Window group, there is an option for “Freeze Panes.” This will let you keep the top row or the first column frozen while you scroll through the remainder of your data. You can also use the “freeze panes” option to freeze multiple rows and columns at the same time. To do this, make sure you select the first cell that you want to be mobile (i.e. the first cell that you want to scroll), then click “freeze panes”. More information can be found here.
The format for referencing a cell located in another sheet is: [SheetName]![Column][Row]. For example, Sheet1!A1. If you want to reference a range of cells, you can use this format: [SheetName]![Column][Row]:[Column][Row]. For example, Sheet1!A1:A50.
To get the appropriate reference, you can also copy the cell, right-click on the cell where you want to place the reference, choose the “paste link” option (which looks like a clipboard with a chain; it will be under “paste special” and “other paste options”). This will give you a formula linking to that cell with an absolute reference; remove the equals sign (=), and that is your cell reference. Note that this only works with single cells; you cannot use “paste link” to create a reference to a range of cells.
More information is available here.
There are two ways to find (and remove) duplicate data values: (1) use conditional formatting to highlight duplicate values; or (2) use the “remove duplicates” command on the Data tab, data tools group. Note that when you use “remove duplicates”, the duplicate data will be permanently deleted. More information can be found here and here.
You can create two types of tables with Excel: (a) Excel tables, which make formatting as well as sorting and filtering data easy; and (b) data tables, which allow for advanced what-if data analysis. This article explains how to create a table within your Excel sheet. Another tutorial is available here. This article explains how to use data tables to perform data analysis.
The VLOOKUP function is used to find things in a table or a range, and it works sort of like a database or even a phone book. For example, you can use VLOOKUP to find an employee’s last name by using her employee ID number, or to find the employee’s telephone number by using her last name. More information is available here.
Pivot Tables are a great way to summarize, analyze, and present your data. You can find a tutorial for Pivot Tables and a video of a Pivot Tables workshop on the Data Analysis tab of this guide. More information is also available here.
See this article from Microsoft support for a list of keyboard shortcuts.
Depending on what version of Excel you have on your computer, you may not be able to automatically open files that were created in newer versions of Excel, such as Excel 2016, which is available on computers at the library. Microsoft’s Office Compatibility Pack will allow you to open such files in whatever version of Excel that you have on your computer. You can download the compatibility pack here.
Excel’s ribbons (or tabs) can sometimes be confusing. If you are having difficulty figuring out where a particular command is located, you can search Microsoft’s Excel support for the command that you are looking for; the articles will almost always tell you where the command is located on the ribbon. Another resource is this webpage, which provides a description of what is located on which ribbon (in Excel for Windows).
Penn’s Information Systems and Computing Department provides support for installing Microsoft Office (including Excel) on your personal computer using your Penn O365 account. Information is available here.
Excel for Mac is generally exactly the same as using Excel in Windows. The difference is that some commands and buttons may be located in different places, on different ribbons, and/or may have a different name. In terms of functionality, the primary difference with the most recent versions of Excel is that Excel for Macs does not have the ability to create and use Pivot Charts. Microsoft provides a guide for using Excel for Mac here. Additional resources comparing Excel for Mac and Windows are available here and here.
Contact Weigle Information Commons with any and all questions you may have about using Microsoft Excel. We can be reached by phone at 215-898-7555 or through our Questions and Comments form.