Advanced Excel – First lecture
Introduction to Advanced Excel learning
Microsoft Excel is program designed to efficiently manage spreadsheets and analyze data. It contains both basic and advanced features that anyone can learn. Once some basic features are known, learning the advanced tools becomes easy.
This lesson is composed of some advanced Excel features. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization.
Objectives of advanced Excel – first lecture:-
1) Learn how to Customize the Interface
Most of Excels interface can be customized to fit many people’s needs. For some, customization makes tools more readily available by placing those tools in a location that is more natural for the user.
This section will introduce you to customizing Excel’s interface by adding a tab in the ribbon, customizing the status bar at the bottom of the program, opening separate panes, and scrolling through a sheet with a static column or row.
Adding a Tab in the Ribbon
In this subsection, we will explore the tab options in Excel. Tabs in Excel can be added, deleted and even reorganized.
This feature is useful if you use a set of tools frequently as it can save time navigating between tabs.
- Download and unzip the Exercise files from the At Lab’s website.
- Open Excel and choose the blank workbook.
- Open backstage view by clicking on the File Tab and choose ‘Options’. This will open the Options dialog box.
- Select ‘Customize Ribbon’. This tab allows you to add tabs as well as move tabs around.
- Click Home under the main tabs and click ‘New Tab’.
A new tab labeled “New Tab” along with a new group labeled “New Group” is placed under the Home tab. From here you can re-name the tab and group, add more groups, and add actions in each group.
- Rename the tab to “Yourname’s Tab” by selecting the tab and choosing
‘Rename’ at the bottom of the ‘Main Tabs’ section.
A dialog box displaying an editable field is displayed. This tool can be used not only to rename your custom tab, but also any tab that is in the worksheet.
- Click OK to accept the new name.
- Rename the new group to “Other Formatting Tools”.
Notice the dialog for renaming a group looks different because it allows you to add symbols to the name.
- Click Ok to accept this name for the group.
- Add the ‘Format Cells…’ tool to your group by selecting the tool in the command window and clicking ‘Add’.
- Click on ‘Formatting Tools (custom)’ and click ‘New Group’ to add a new group to your tab.
- Rename this group to “Image Tools”.
We will be adding some tools that not already in the ribbon here.
- Select ‘Commands Not in the Ribbon’ from the commands drop down menu
and add the ‘As Picture’, ‘Camera’, ‘Image(ActiveXControl)’, ‘Less Brightness’, and ‘Less Contrast’ tools into the ‘Image Tools’ group.
- Click and drag your tab to before ‘Home’.
In the ribbon, the tabs are displayed in the same order as in the ‘Customize Ribbon’
dialog box, so this will place your tab as the first tab after the file tab.
- Click ‘Ok’ to exit the ‘Customize Ribbon’ dialog box and click on your custom tab.
Your tab should look similar to the following picture.
Status Bar Customization
At the bottom of every Microsoft Office program, there is a status bar. This bar, by default, contains different viewing options as well as the zoom slider.
Excel allows you to add and take away features on the status bar such as the ability to display the sum, average, and other calculations for a selected data range.
- Open a new worksheet and observe the status bar.
- Right click on the status bar to see the different options available.
- Ensure that Average, Count, Minimum, Maximum, Sum, Num Lock are selected.
Before any data is typed, the status bar is already displaying Num Lock on the left side to indicate that the Num Lock is on.
- Enter the numbers 1-12 in A1:A12.
We will look at some different calculations of these numbers in the status bar.
- Select cells A1:A12.
Notice the status bar now displays all of the calculations that were selected. This can be used whenever it is useful to know the average, sum, etc. but not necessary to display that information in the sheet.
Navigating in Multiple Windows and Panes
When working with multiple worksheets, navigating through each sheet can be an annoyance as well as cost valuable time. On the other hand using window views allows you to navigate in multiple workbooks at the same time. You can also split one workbook into 4 different views or open the same worksheet multiple times. We will explore these features now.
- Close Excel.
In the past, Excel was able to run regardless of whether a workbook was open or not. In
2013, Excel started handling each workbook as its own instance of the program.
- Navigate to the ‘Exercise Files’ folder.
- Open the Divisional_Sales, Quarterly Sales by Division, Yearly_sales, and
- Click on the ‘Restored Down’ button between ‘Minimize’ and ‘Close’.
Notice that you can see two windows of Excel open, and each Window contains all of the tools and functions in Excel. As mentioned before, each workbook used to be a part of the program, now, the program is open inside each workbook. This is useful when arranging multiple worksheets because different tabs on the ribbon can be opened in each workbook.
- Navigate to the Window group in the View tab.
- Click Arrange all.
Notice a dialog box is displayed giving a few different displaying options. We will explore each of these options.
- Choose Tiled and click OK.
Tiled shows the workbooks in equally sized squares with one taking two spaces on the left if there are an odd number of windows. This is great for easy comparison of data in different windows.
- Click Arrange All and choose Horizontal.
This aligns the windows one on top of the other with each workbook as wide as the program’s window with each having the same height as the rest. This choice is useful if you want to compare rows in different workbooks.
- Restore one of the instances to make the ribbon reappear.
- Choose the Vertical option in the Arrange All dialog box.
The workbooks are now aligned next to each other so that they are all as tall as the program and all have the same width. This allows you to compare columns from multiple workbooks easily.
- Choose the Window drop-down to display the window group.
- For a final time, choose Arrange All and select the Cascade option.
This places the active workbook in the front with the other workbooks cascaded behind it. This allows you to easily move between books one at a time while still given you a large viewing window for each book.
- Click the Quarterly Sales by Division workbook and then, click View Side by Side.
A dialog box asking you to choose another workbook is displayed. This tool only allows viewing of two workbooks at once and by default is similar to the Horizontal Arrange All tool. If multiple windows were arranged just before this tool was used, the tool will sometimes try to match the other windows arrangement.
- Choose the Divisional_Sales workbook.
Your window should look like the following image.
- Observe that Synchronous Scrolling is highlighted and scroll down a worksheet.
- Click Hide.
This hides the active window. If pressed multiple times, it will continue hiding windows until all the windows are hidden.
- Click hide again until there are no worksheets displayed.
Notice only the program is running, and no worksheet is showing.
- Click Unhide.
A dialog box showing the hidden workbooks is displayed.
- Choose the Divisional_Sales.xlsx workbook and click ‘OK’.
- Click New Window.
A new window display a copy of the active workbook is displayed. The New window button can be used multiple times to add multiple windows of the same workbook. Each window is titled with the workbooks name followed by a colon and a number.
- Arrange the windows side-by-side.
- Type “1234” into cell A1 of Divisional_Sales.xlsx:2
When something is done to one window of a worksheet, the changes are displayed in all of the windows of the worksheet.
- In the top window, click into the North Division – January sheet.
These two sheets can now be compared without switching between sheets in the same window.
We have just looked at how to view multiple workbooks and sheets simultaneously in Excel. With this information, using multiple workbooks or sheets at the same time becomes less of a task. Excel also allows the ability to split one window into multiple sections called panes which we will look at in the next section.
Using a Static Column or Row
Static columns and rows involve the use of panes. A pane is a divided section of a worksheet in the same window. When a window is divided into panes, scrolling comparing parts of the same worksheet becomes easier. We will look at different ways to display panes.
- Close all but the ‘Units_per_Customer.xlsx’ workbook and maximize the
This workbook contains information that is spread over many columns and rows. To view different parts of this sheet, will use the split tool.
- Click Split in the Window group on the View tab.
Excel splits this worksheet into 4 panes with a gray border between each pane. Each pane is equally sized, but you can adjust the borders if needed. By default, each pane shows a different part of the worksheet.
- Scroll up and down in the top half of the screen.
The top panes both scroll vertically together and the bottom panes scroll vertically together.
- Scroll left and right in the right half of the screen.
Similar to vertical scrolling, the left and right halves of the screen scroll horizontally together.
- Click into any other sheet.
Notice that only the first sheet is split into panes. Each sheet can be split into panes at any time by navigating to the sheet and clicking Split.
- Navigate back into sheet 1 and click Split a second time.
This brings the window back to the original view. A sheet will remain in the split view until Split is clicked and second time. Another way of using panes is by freezing the panes.
- Click into cell A1.
- Click Freeze Panes.
A menu giving three options is shown. Each of these options creates a set of panes and freezes one of the panes to allow for easy viewing of data from one part of the sheet with the frozen pane.
- Choose the first option, ‘Freeze Panes’.
- Click Freeze panes and choose ‘Unfreeze Panes’.
Anytime you have panes frozen, this option will unfreeze the panes and restore the sheet to the default view. You can also adjust where you want the top pane placed.
- Select cell L23 and Freeze the panes a second time.
Notice this time, the first pane is now larger than before. Anytime a cell other than A1 is selected, the panes are positioned so that the last selected cells are not in the static frame.
- Unfreeze the panes, select Row 8, and refreeze the panes.
Now, the frozen pane is simply the top seven rows with the selected row just out of the static pane.
- Unfreeze the panes, select column C, and refreeze the panes.
Here, columns A and B are static while the selected column is just out of the range. We will briefly look at freezing just the top row or left column.
- Unfreeze the panes and choose ‘Freeze Top Row” in the freeze panes menu.
- Scroll down the page.
Here the top pane is static. This allows easier data entry in the table. It also allows information on specific people to be accessed easier.
- Now choose Freeze ‘First Column’.
This time, the first column is the only static pane.
- Unfreeze the panes and close this workbook.
When it is necessary to navigate within large worksheets, workbooks, or multiple worksheets and workbooks, using windows can save time from switching between sheets and books as well as from scrolling back and forth from one part of the sheet to another. In the next section, we will see how referencing across sheets can also save time.
Referencing Across Sheets
Anytime a cell is used in another part of a workbook or worksheet, the cell is said to be referenced to that part. An example is when a formula references a range of cells. In AT Steps Excel Essentials lesson, referencing is introduced within one worksheet. Here, referencing will be explored across multiple worksheets in a workbook and even across multiple workbooks.
- Open the Divisional_Sales.xlsx worksheet.
This worksheet contains divisional sales for a fictitious company. The first four sheets have information on the sales in each state. We will be entering formulas for Excel to place the totals inside one sheet.
- Navigate to the January Totals worksheet.
This sheet is formatted to display the divisional total sales and then overall totals at the bottom of the sheet.
- Click into cell B3.
Here we want to display the total units sold from the ‘South Division – January’ worksheet. This could be done by copying that data directly to the totals sheet, but if the data is changed in the original sheet, the changes would not be reflected in the totals sheet. The solution to this problem would be to relatively reference cell A13 from the original worksheet.
- Type “=(“.
- Navigate to the worksheet labeled ‘South Division – January’ and choose cell E13.
Notice the formula bar now displays “=(‘South Division – January’!E13” This is how Excel labels cells from other worksheets in the same workbook.
- Type ‘)’ and press enter.
‘January Totals’ is now the active worksheet, and the data displayed in cell B3 is identical to the data in cell E13 on the South Division – January worksheet.
- Change the data in cell E13 on the South Division – January worksheet and refer
back to the January Totals sheet.
Here, the data has changed to match what is displayed in the worksheet you are referencing.
- Press Undo to undo the change of data in cell E13 on the ‘South Division – January’ worksheet.
- Repeat steps 3-6 for cells B4:B6 in the Totals sheet using the data from the rest of
the sheets respectively.
At the end of this step, your sheet should look like the following picture:
- Type ”=sum(“ into cell B7 in the Totals sheet.
We will use this formula to sum the totals in E13 from each sheet.
- Click into the first worksheet and click cell E13.
- Now hold ‘shift’ and click on the worksheet just before the totals sheet.
Notice the status bar now displays “=SUM(‘South Division – January: West Division – January’!E13”. This can be broken into two parts. The first part is “’South Division – January: West Division – January’!” which tells Excel that we are referencing cells from all of the sheets between ‘South Division – January’ and ‘West Division – January’ should sheets be moved so that there are more sheets between these sheets, excel will update the formula to include the additional sheets. The second part, “E13”, tells Excel which specific cells are referenced inside the range of worksheets.
- Type ‘)’ and press enter.
‘January Totals’ is again the active sheet, and now cell B7 now contains the total from
E13 in the other sheets.
- Continue referencing the appropriate cells for the rest of this table.
To practice referencing multiple sheets in a specific cell, use cells F13 and G13 in the other sheets for cells C7 and D7 in the ‘January Totals’ worksheet respectively. Your
‘January Totals’ worksheet should now look like the following picture.
- Open the Yearly_sales.xlsx workbook and display the two workbooks side-by- side.
This sheet contains the yearly totals based on the month. We want to reference the totals in the ‘Divisional_Sales’ workbook.
- Type ‘=(‘ into cell B3 on the ‘2012 Sales’ worksheet.
- Now click into cell B7 on the ‘January Totals’.
- Type ‘)’ and press enter to return to the original workbook.
We want to drag this reference through the rest of the row, but we want to change the reference from an absolute reference to a relative reference.
- Click into cell B3 and then into the formula bar.
- Remove the ‘$’ from “=(‘[Divisional_Sales.xlsx]January Totals’!$B$7)”.
Now that we have a relative reference, we can drag this through the rest of the row which will fill in the rest of the information with respect to the ‘January Totals’ sheet.
- Drag the reference through the row.
The values in cells C3 and D3 now correspond to cells C7 and B7 in the ‘January Totals’ sheet.
In this section of advanced Excel learning, we explored relative referencing across sheets and workbooks. We also saw that one cell can reference more than one sheet. In the next section we will see some formatting features that also involved multiple sheets.