In the last installment of “Quick Excel Tips & Tricks,” Stephanie taught the webinar participants “How to be an Excel Secret Agent.” From protecting worksheets to hiding information in spreadsheets, Stephanie gave the scoop on how to finesse your way around an Excel workbook.
Note: Remember there are several ways to do the tricks Stephanie mentions, but she shares the way(s) that she finds to be easiest. Also, remember that when you insert anything, Excel will insert it above (for rows) and to the left (for columns!)
SESSION 3 TABLE OF CONTENTS
HIDING EXCEL INFORMATION
CREATING PIVOTTABLES
CREATING PIVOTCHARTS
HIDING EXCEL INFORMATION
Sometimes you have information in your spreadsheet that you need to calculate something, but you don’t need anyone else to see. Excel makes it easy to hide aspects of your data!
-
HIDING COLUMNS: (5:51)
- Highlight the column you would like to hide.
- Right-click.
- Select Hide. Your columns should go away.
The same process applies to rows.
-
UNHIDING COLUMNS (6:25)
Note: Anyone can unhide or hide columns if they notice that, for example, your columns skip from G to J.
- Double-click on the double line between the column letters. The double line should change and now have two arrows on each side of it.
- Double-click to show a column that is hidden. Every time you double-click, a new column will appear.
Or
- Highlight the two columns that have hidden information between them.
- Right-click.
- Select Unhide. Your columns should reappear.
The same process applies to rows.
-
HIDING SHEETS (8:20)
- Right-click on the sheet you would like to hide.
- Select Hide.
Figuring out if a sheet is hidden is not as intuitive as figuring out if a column or row is hidden. However, someone could right-click on a worksheet that is visible, choose Unhide, and your hidden sheets are listed there. If you need to unhide a sheet, choose the sheet from the list and click ok.
-
LOCKING EXCEL WORKSHEETS (9:40)
Note: Stephanie suggests saving a spreadsheet you lock in a separate document as “the locked file,” or something along those lines, so you have another version that is unlocked…just in case you were unable to get back into your locked version!
To protect your worksheets (so that no can alter the data):
- Click on the Review tab (located between the Data and View tabs at the top of your workbook).
- Select Protect Sheet.
- A pop-up menu will appear. More than likely, the top two boxes under “Allow all users of this worksheet to” will already be checked. (These are the Select locked cells and Select unlocked cells boxes). You will want to uncheck these two boxes because you don’t want the user to be able to select anything.
- Make sure the top box, “Protect worksheet and contents of locked cells,” under “Protect Sheet,” is checked.
- Enter in a password in the “Password to unprotect sheet” section.
- Click ok. A box will appear asking you to reenter the password to proceed. There will also be a note of caution alerting you that if you lose or forget the password, it cannot be recovered. Stephanie advises that you write down the password (or keep it somewhere easily accessible).
- Reenter your password.
- Click ok.
Note: Only the singular sheet will be protected, not the entire workbook.
When you are in the Protect Sheet menu, be aware that there are certain things you can check and allow users to still do, if you wish to do so. For example, you could allow the user to format rows but do nothing else.
-
UNLOCKING EXCEL WORKSHEETS (12:08)
Let’s say you have protected your worksheet and sent it out, but now you need to go in and make further changes to your data. To unlock your protected worksheet:
- Click on the Review tab (located between the Data and View tabs at the top of your workbook).
- Select Unprotect Sheet.
- Enter the password.
- Click ok.
CREATING PIVOTTABLES
A PivotTable allows you to change your view. You can manipulate your data easily without having to go in and make separate tables each time the data is manipulated. PivotTables are great for when you are looking at data you want to compare in some way.
-
INSERTING PIVOTTABLES (14:26)
- Make sure that you are in your data to be able to insert a PivotTable.
- Click on the Insert tab (located between the Home and Page Layout tabs at the top of your workbook).
- Select PivotTable (located in the Tables section on the left side of your Insert tab).
- A menu will appear that says Create PivotTable. You will be able to select the data you want to work with, either internally or through an external data source. You can also choose if you want your PivotTable to be in a new worksheet or next to your current one, along with whether you want to analyze multiple tables. Select your options and click ok.
-
SELECTING FIELDS FOR YOUR PIVOTTABLE (15:25)
When you put in a PivotTable, the PivotTable box will appear on the left of your spreadsheet, indicating that a PivotTable will be built for you. On the right side, a menu with PivotTable fields will appear that shows you which fields you can put into your table. To select your fields:
- Use the Search bar to quickly maneuver through fields if you have a lot of them. Otherwise, you can use your scroll bar underneath the Search bar.
- Below your scroll bar, there are boxes that allow you to show what you are going to put into your columns, rows, if there is data you want to add up using values, etc. Click and drag options from your scroll menu into the boxes you wish to place the data in. As you click and drag, the PivotTable will begin building itself out.
- If you click out of your data, the PivotTable Fields menu will disappear; click back in your data to have the menu reappear and manipulate your data again.
-
ADDING FILTERS TO YOUR PIVOTTABLE (17:32)
To apply a filter to your data:
- Make sure you are in your data and that the PivotTable Fields Menu has appeared.
- Click and drag what you would like to filter to the Filters box in the PivotTable Fields Menu.
- At the top of your PivotTable, a row will appear with the name of what you are filtering (for example, if you were filtering by item, Item) in the A column, and a drop-down menu with an arrow will appear in the B column. Click on the down arrow and select what would like to further filter (for example, pens). You do have the option to select multiple items. Click ok.
- Your filtered table will appear in your worksheet.
Note: If you change a value in your original worksheet, the value in the PivotTable will also change; just make sure you click on your PivotTable and select “Refresh” when you go to look for the updated value.
If you double-click a cell on your PivotTable, Excel will pull the information and create a new sheet with that information.
You can also hover over your cells to see the value, as well as which row and column the value is coming from.
CREATING PIVOTCHARTS
A PivotChart also allows you to manipulate and view data differently, but instead of putting it into a table, Excel puts the data into a chart!
-
INSERTING PIVOTCHARTS (25:12)
- Make sure that you are in your data to be able to insert a PivotChart.
- Click on the Insert tab (located between the Home and Page Layout tabs at the top of your workbook).
- Select PivotChart (located in the Charts section in the middle of your Insert tab).
- Choose the PivotChart option to only select a PivotChart. (You also have the option to Select PivotChart & PivotTable and insert both simultaneously).
- A pop-up menu will appear that says Create PivotChart. You will be able to select the data you want to work with, either internally or through using an external data source. You can also choose if you want your PivotChart to be in a new worksheet or next to your current one, along with whether you want to analyze multiple tables. Select your options and click ok.
-
SELECTING FIELDS FOR YOUR PIVOTCHART (25:36)
When you put in a PivotChart, the PivotTable box will appear on the left of your spreadsheet. Even though it says PivotTable, it is indicating that a PivotChart will be built for you. On the right side, a menu with PivotChart fields will appear, which shows you what fields you can put in your table. To select your fields:
- Use the Search bar to quickly maneuver through fields if you have a lot of them. Otherwise, you can use your scroll bar underneath the Search bar.
- Below your scroll bar, there are boxes that allow you to manage what you are going to put into your legend, axis, if there is data you want to add up using values, etc. All you have to do is click and drag options from your scroll menu into the boxes you wish to place the data in. As you click and drag, the PivotChart will begin building itself out.
- If you click out of your data, the PivotChart Fields menu will disappear; click back in your data to have the menu reappear and manipulate your data again.
Note: You can take data from your main spreadsheet or from the information built into a PivotTable you previously built.
-
ADDING FILTERS TO YOUR PIVOTCHART (26:02)
To apply a filter to your data:
- Make sure you are in your data and that the PivotChart Fields Menu has appeared.
- Click and drag what you would like to filter into the Filters box in the PivotChart Fields Menu.
- At the top of your PivotChart, a tiny drop-down menu will appear with the name of what you are filtering (for example, if you were filtering by region, Region) at the top of your chart. Click on the down arrow and select what would like to further filter (for example, Central). You do have the option to select multiple items. Click ok.
- Your filtered chart will appear in your worksheet.
Learn more about PivotCharts, PivotTables, and numerous other Excel features by taking one of Stephanie’s courses.
ABOUT THE AUTHOR:
Hi! My name is Marah Whitaker (think Laura with an M). I am the Marketing Assistant for UTC Center for Professional Education. During the workday, I spend time writing blog posts, creating content for social media, developing email campaigns, and building relationships with our customer base. During my free time, you can find me getting lost in a good book, having spontaneous dance parties, playing piano, and going to Buffalo Wild Wings on Wing Night. Professionally and personally, I aspire to live by the Mr. Feeny quote, “Dream. Believe. Try. Do Good.” I strive to use my passions to serve others and contribute positively to the world around me.
Connect with me on LinkedIn.