Excel Simplified: How to Use Excel at Ease by Yosef Andreas

Author:Yosef Andreas
Language: eng
Format: azw3, epub
Published: 2016-05-31T07:00:00+00:00

Now, let’s do some cleanup. Remove the gridlines and legends. Don’t forget to add the chart title: Trends 100

90

80

70

60

50

40

30

20

10

0

1 2 3 4 5 6 7 8 9 10

That’s it. The banding area inside the chart that help you visualize the acceptance area.

VBA

“Tackle the boredom task” VBA is Excel’s powerful feature that is commonly under-utilized. Yet, it can help you getting the tedious jobs done in efficient time. It cuts the repeated action into one single command (known as macro) that you can execute, as you need it. There are two ways to setup the macro: using the macro recorder or type the code directly from Visual Basic Editor.

Macro recorder is easy to understand. It records the actions you perform and then store them into a small workbook named PERSONAL.XLSB. To use the recorded macro, you can either access it from Developer menu or hit the customized shortcut that is previously assigned during the macro recording. Before we dig into macro setup, let’s make the Developer menu available onto the ribbon. If you are using Excel 2007, go to Office Button and select Excel Options. Make a tick mark on the “Show Developer tab in the Ribbon”.

For Excel 2010 and above, the location is different. Select Customize Ribbon and then make a tick mark on the Developer tab.

Your Developer tab will appear on the ribbon as follow:

Now, let’s make your first macro. As example, I want to apply Cell Style with center alignment – both vertically and horizontally. Prior to do this, make sure that you have selected “Use Relative References” on the Developer tab so that you can apply the macro at any cell.

Now, click the macro recorder button:

A Record Macro box will pop out. You can name the macro and assign the shortcut key to activate it. Quick tips: combine the shortcut with Shift key as it will override the Office native shortcut (for example, you cannot use Ctrl+B to make cell content bold if it is being used for macro shortcut).

Don’t forget to select Personal Macro Workbook as storage so you can use it to any workbook. You can also type the description of the macro to help you remember its action. When you are done, click OK. Now the macro is being recorded (indicated by the changing icon of the recorder):

Select any cell, and then go to File, Cell Style, Accent1.

Go to Format Cells (press Ctrl+1), and then select Center for both Horizontal and Vertical Alignment. Click OK.

To finish the recording process, click stop recording:

Congratulation! You just created your first macro. Now, let’s try to apply it. Select the cell(s), and then press Alt+F8:

Select the macro you have just created, and then double click on it (you can also select Run or hit the shortcut key), the cells style change automatically like the picture below:

The other way to create a macro is from Visual Basic Editor, which is more flexible than macro recording as you want to take full control of the code. To launch the Visual Basic Editor, go to Developer tab and select Visual Basic or just press Alt+F11 as its shorcut.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.