Using Macros in Excel

Macros in MS Excel

Macros enable you to automate almost any task that you can undertake in Excel 2010. By using macro recorder from View Tab » Macro Dropdown to record tasks that you perform routinely, you not only speed up the procedure considerably but you are assured that each step in a task is carried out the same way each and every time you perform a task.

To view macros choose View Tab » Macro dropdown.

View Macros option

Macro Options

View tab contains a Macros command button to which a dropdown menu containing the following three options.

  • View Macros − Opens the Macro dialog box where you can select a macro to run or edit.

  • Record Macro − Opens the Record Macro dialog box where you define the settings for your new macro and then start the macro recorder; this is the same as clicking the Record Macro button on the Status bar.

  • Use Relative References − Uses relative cell addresses when recording a macro, making the macro more versatile by enabling you to run it in areas of a worksheet other than the ones originally used in the macro’s recording.

Creating Macros

You can create macros in one of two ways −

  • Use MS Excel’s macro recorder to record your actions as you undertake them in a worksheet.

  • Enter the instructions that you want to be followed in a VBA code in the Visual Basic Editor.

Now let’s create a simple macro that will automate the task of making cell content Bold and apply cell color.

  • Choose View Tab » Macro dropdown.
  • Click on Record Macro as below.

Record Macro

  • Now Macro recording will start.

  • Do the steps of action, which you want to perform repeatedly. Macro will record those steps.

  • You can stop the macro recording once done with all steps.

Stop recording

Edit Macro

You can edit the created Macro at any time. Editing macro will take you to the VBA programming editor.

Edit Macros

Using Templates in Excel

Using Templates in MS Excel

Template is essentially a model that serves as the basis for something. An Excel template is a workbook that’s used to create other workbooks.

Viewing Available Templates

To view the Excel templates, choose File » New to display the available templates screen in Backstage View. You can select a template stored on your hard drive, or a template from Microsoft Office Online. If you choose a template from Microsoft Office Online, you must be connected to the Internet to download it. The Office Online Templates section contains a number of icons, which represents various categories of templates. Click an icon, and you’ll see the available templates. When you select a template thumbnail, you can see a preview in the right panel.

Using templates

On-line Templates

These template data is available online at the Microsoft server. When you select the template and click on it, it will download the template data from Microsoft server and opens it as shown below.

View templates

Adding Graphics in Excel

Graphic Objects in MS Excel

MS Excel supports various types of graphic objects like Shapes gallery, SmartArt, Text Box, and WordArt available on the Insert tab of the Ribbon.Graphics are available in the Insert Tab. See the screenshots below for various available graphics in MS Excel 2010.

Various Graphics in MS Excel

Insert Shape

  • Choose Insert Tab » Shapes dropdown.

  • Select the shape you want to insert. Click on shape to insert it.

  • To edit the inserted shape just drag the shape with the mouse. Shape will adjust the shape.

Inserting Shape

Insert Smart Art

  • Choose Insert Tab » SmartArt.

  • Clicking SmartArt will open the SmartArt dialogue as shown below in the screen-shot. Choose from the list of available smartArts.

  • Click on SmartArt to Insert it in the worksheet.

  • Edit the SmartArt as per your need.

Inserting SmartArt

Insert Clip Art

  • Choose Insert Tab » Clip Art.

  • Clicking Clip Art will open the search box as shown in the below screen-shot. Choose from the list of available Clip Arts.

  • Click on Clip Art to Insert it in the worksheet.

Inserting Clip Art

Insert Word Art

  • Choose Insert Tab » WordArt.

  • Select the style of WordArt, which you like and click it to enter a text in it.

Cross Referencing in Excel

Graphic Objects in MS Excel

When you have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. This is where the Vlookup function comes into its own.

(toc)

VLOOKUP

VlookUp searches for a value vertically down for the lookup table. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) has 4 parameters as below.

  • lookup_value − It is the user input. This is the value that the function uses to search on.

  • The table_array − It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need.

  • Col_index_num − It is the column of data that contains the answer that you want.

  • Range_lookup − It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value be formatted in ascending order.

VLOOKUP Example

Let’s look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth, and the second shows their favorite color. How do we build a list showing the person’s name, their date of birth and their favorite color? VLOOOKUP will help in this case. First of all, let us see data in both the sheets.

This is data in the first sheet

Data in First Sheet

This is data in the second sheet

Data in Second Sheet

Now for finding the respective favorite color for that person from another sheet we need to vlookup the data. First argument to the VLOOKUP is lookup value (In this case it is person name). Second argument is the table array, which is table in the second sheet from B2 to C11. Third argument to VLOOKUP is Column index num, which is the answer we are looking for. In this case, it is 2 the color column number is 2. The fourth argument is True returning partial match or false returning exact match. After applying VLOOKUP formula it will calculate the color and the results are displayed as below.

VLOOKUP results

As you can see in the above screen-shot that results of VLOOKUP has searched for color in the second sheet table. It has returned #N/A in case where match is not found. In this case, Andy’s data is not present in the second sheet so it returned #N/A.

Printing Worksheets in Excel

Quick Print

If you want to print a copy of a worksheet with no layout adjustment, use the Quick Print option. There are two ways in which we can use this option.

(toc)

  • Choose File » Print (which displays the Print pane), and then click the Print button.

  • Press Ctrl+P and then click the Print button (or press Enter).

Printing WorkSheet

Adjusting Common Page Setup Settings

You can adjust the print settings available in the Page setup dialogue in different ways as discussed below. Page setup options include Page orientation, Page Size, Page Margins, etc.

  • The Print screen in Backstage View, displayed when you choose File » Print.

  • The Page Layout tab of the Ribbon.

Choosing Your Printer

To switch to a different printer, choose File » Print and use the drop-down control in the Printer section to select any other installed printer.

Setting Printer

Specifying What You Want to Print

Sometimes you may want to print only a part of the worksheet rather than the entire active area. Choose File » Print and use the controls in the Settings section to specify what to print.

  • Active Sheets − Prints the active sheet or sheets that you selected.

  • Entire Workbook − Prints the entire workbook, including chart sheets.

  • Selection − Prints only the range that you selected before choosing File » Print.

Setting what to print

Email Workbooks MS Excel

Email Workbook

MS Excel allows you to email the workbook very easily. To email the workbook to anyone, follow the below mentioned steps.

  • Choose File » Save and Send. It basically saves the document first and then the emails.

Emailing Workbook

  • Click on Send using E-mail if your email system is configured. MS Outlook will open with the file as attachment in the New Email Window. You can send mail this workbook to anyone with valid email address.

Outlook Window

Explore Windows in PowerPoint

The following screenshot shows the various areas in a standard PowerPoint file. It is important to familiarize yourself with these areas as it makes learning and using PowerPoint easier.

Explore PowerPoint

File Tab

This tab opens the Backstage view which basically allows you to manage the file and settings in PowerPoint. You can save presentations, open existing ones and create new presentations based on blank or predefined templates. The other file related operations can also be executed from this view.

Ribbon

PowerPoint Ribbon

The ribbon contains three components −

  • Tabs − They appear across the top of the Ribbon and contain groups of related commands. Home, Insert, Page Layout are examples of ribbon tabs.

  • Groups − They organize related commands; each group name appears below the group on the Ribbon. For example, a group of commands related to fonts or a group of commands related to alignment, etc.

  • Commands − Commands appear within each group as mentioned above.

Title Bar

This is the top section of the window. It shows the name of the file followed by the name of the program which in this case is Microsoft PowerPoint.

Slide Area

This is the area where the actual slide is created and edited. You can add, edit and delete text, images, shapes and multimedia in this section.

Help

The Help Icon can be used to get PowerPoint related help anytime you need. Clicking on the “?” opens the PowerPoint Help window where you have a list of common topics to browse from. You can also search for specific topics from the search bar at the top.

PowerPoint Help

Zoom Options

The zoom control lets you zoom in for a closer look at your text. The zoom control consists of a slider that you can slide left or right to zoom in or out, you can click on the – and + buttons to increase or decrease the zoom factor. The maximum zoom supported by PowerPoint is 400% and the 100% is indicated by the mark in the middle.

Slide Views

The group of four buttons located to the left of the Zoom control, near the bottom of the screen, lets you switch between PowerPoint views.

  • Normal Layout view − This displays page in normal view with the slide on the right and a list of thumbnails to the left. This view allows you to edit individual slides and also rearrange them.

  • Slide Sorter view − This displays all the slides as a matrix. This view only allows you to rearrange the slides but not edit the contents of each slide.

  • Reading View − This view is like a slideshow with access to the Windows task bar in case you need to switch windows. However, like the slideshow you cannot edit anything in this view.

Notes Section

This sections allows you to add notes for the presentation. These notes will not be displayed on the screen during the presentation; these are just quick reference for the presenter.

Quick Access Toolbar

The Quick Access Toolbar is located just under the ribbon. This toolbar offers a convenient place to group the most commonly used commands in PowerPoint. You can customize this toolbar to suit your needs.

Slide Tab

This section is available only in the Normal view. It displays all the slides in sequence. You can add, delete and reorder slides from this section.

Backstage View in PowerPoint

In Office 2010, Microsoft replaced the traditional file menu with the new Backstage view. This view not only offers all the menu items under the file menu, but additional details which makes management of your files a lot easier.

Accessing Backstage View

You can access the Backstage view simply by clicking on the File tab. You can exit this view by clicking on any tab (including the File tab again). You can also press the ‘Esc‘ button on the keyboard.

PowerPoint Backstage

Organization of Backstage View

The backstage view has three sections or panes.

PowerPoint Backstage

  • First Pane − This is the commands pane which consists of all the commands you would typically find in the file menu of older versions. You also have the Options menu which lets you edit the options on the program like customizing the ribbon.

Various commands under the first pane are described in the table below −

S.No Command & Description
1

Save

This allows you to save a new file or an existing file in standard format. If you are working on a previously saved file this will save the new changes in the same file format. If you are working on a new file, this command would be similar to the Save As command.
2

Save As

Allows you to specify the file name and the file type before saving the file.
3

Open

Allows you to open new PowerPoint files.
4

Close

Allows you to close an existing file.
5

Info

Displays the information about the current file.
6

Recent

Lists series of recently viewed or edited PowerPoint files.
7

New

Allows you to create a new file using blank or pre-defined templates.
8

Print

Allows you to select the printer settings and print the presentation.
9

Save & Send

Allows you to share your presentation with larger audience via emails, web, cloud services, etc.
10

Help

Provides access to PowerPoint Help.
11

Options

Allows you to set various options related to PowerPoint program.
12

Exit

Closes the presentation and exits the program.

  • Second Pane − This is the subcommands pane. This will list all the commands related to the main command you choose in the first pane. For example, if you select Print in the first pane, you get to choose the printer and adjust the print settings in the second pane.

  • Third Pane − This is the preview or file information page. Depending on the command and the subcommand you select, this pane will either display the properties of the file or give you a preview of the file.

How to Create Presentation in PowerPoint?

PowerPoint offers a host of tools that will aid you in creating a presentation. These tools are organized logically into various ribbons in PowerPoint. The table below describes the various commands you can access from the different menus.

Excel Window

Menu Category Ribbon Commands
Home Clipboard functions, manipulating slides, fonts, paragraph settings, drawing objects and editing functions.
Insert Insert tables, pictures, images, shapes, charts, special texts, multimedia and symbols.
Design Slide setup, slide orientation, presentation themes and background.
Transitions Commands related to slide transitions.
Animations Commands related to animation within the individual slides.
Slide Show Commands related to slideshow set up and previews.
Review Proofing content, language selection, comments and comparing presentations.
View Commands related to presentation views, Master slides, color settings and window arrangements.

Besides these depending on the objects selected in the slide, there are other menu tabs that get enabled.

How to Add New Slides in PowerPoint

In this chapter, we will understand how to add new slides in an existing presentation. Here are the steps that allow you to insert a new slide in the deck −

Step 1 − Right-click in the Navigation Pane under any existing slide and click on the New Slide option.

Add New Slide

Step 2 − The new slide is inserted. You can now change the layout of this slide to suit your design requirements.

New PowerPoint Slide

Step 3 − To change the slide layout, right-click on the newly inserted slide and go to the Layout option where you can choose from the existing layout styles available to you.

Microsoft Office 2010

You can follow the same steps to insert a new slide in between existing slides or at the end on the slide list.

When we insert a new slide, it inherits the layout of its previous slide with one exception. If you are inserting a new slide after the first slide (Title slide), the subsequent slide will have the Title and Content layout.

You will also notice that if you right-click in the first step without selecting any slide the menu options you get are different, although you can insert a new slide from this menu too.

Microsoft Office 2010