The alignment section of the Home Tab of the Excel ribbon contains a number of options relating to the way in which your data is position within the cell. Perhaps the most familiar and the most frequently used buttons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you click in the cells of an unformatted worksheet, none of these three icons is highlighted, which indicates that none of them is the default. The reason for this is that Excel treats data differently depending on the data type.
If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To change the horizontal alignment, either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.
Haven chosen one type of horizontal alignment, you can change it in two ways. You can either click on a different form of alignment or click again on the already selected alignment. For example, if your text is centred and you click on the Centre button a second time, this deactivates centre alignment and returns you to the default alignment which, for text, is left. Thus we have, effectively, four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.
Excel also allows you to specify vertical alignment. This setting normally only becomes apparent when you increase the height of the cell and this time there is a definite default which is that text is aligned at the bottom of the cell. This setting applies to text, dates and numbers alike.
To set the vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.
Excel also features the ability to change the orientation of text within the cell. This is particularly useful in those situations where your column headings are wider than the data in the cells. To change the vertical orientation of your text, just select the cells in question and then choose the appropriate angle in the Alignment dialogue.
If you rotate your column headings by 90 degrees, you can usually make the columns much narrower. Excel has a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format then AutoFit Columns. This command makes each of the highlighted columns no wider than it needs to be in order to display all the data it contains.
To create any chart in Microsoft Excel, you must begin by selecting the data that you want to plot including any headings. You then have the choice of creating either a stand-alone chart or an embedded chart. To create a stand-alone chart, right-click on one of your worksheet tabs and choose Insert. Next, click on the chart icon and click OK. Excel creates a chart using the default chart type.
Having just created a chart, you will notice that Excel displays three contextual tabs headed “Chart Tools”. The contextual tabs consist of Design, Layout and Format. The Design contextual tab enables you to change the chart type and the basic information about the chart such as the underlying data.
The Layout tab offers you a more intricate level of control over the various components of your chart. For example, by default, Excel adds a legend to each new chart. If a chart has only one series of data, we don’t need a legend to tell us what the chart colours mean. We can therefore click on the Legend drop-down menu and choose None.
The Format contextual tab gives us the most primitive control over the chart elements. It is here that we are able to click on individual elements and change them at the object level. In other words, we are working on these elements purely as drawing objects rather than as elements of the chart. However, even at this primitive level, Excel still shows us the relationship between the chart elements and the underlying data. Thus, for example, when an individual data point is highlighted, Excel displays the corresponding worksheet address in the formula bar.
When creating a standalone chart, you are not given the option of choosing the chart type during the chart creation process. If you want to change the chart type, you have to do so at a later stage by choosing a Chart Type option in the Design contextual menu.
As regards the chart sheet which contains the chart, it behaves in much the same way as Excel worksheets. For example, it can be renamed or deleted by right-clicking the sheet tab and choosing Rename or Delete from the context menu.
If you would like to learn more about Excel and Excel VBA training courses, visit On Site Training Courses . Com, a UK IT training web site offering Excel Classes all over the UK.
We all have been around computers and some of us use them on a daily basis but we all have to admit that we don’t use all of the programs chances are we don’t even know all of the programs on the computer itself. By taking time to go through Microsoft Excel Training you will have more knowledge of what your program and computer can do for you?
Computers are full of programs that some of us don’t even know about because it just isn’t an everyday program that we use. With Microsoft Excel Training you can now learn these programs and how to use them for what they are worth.
Microsoft Excel is one of those programs that you don’t use on a daily basis unless you have a company or business. Many things can be done with Microsoft Excel such as spreadsheets, charts and more. The program can be fun, exciting and do anything that you want to do with a matter of a few simple steps.
Microsoft Excel training can be very useful and can teach you many things such as what buttons perform which functions and how to use them effectively and to your best ability.
There are many ways to get Microsoft Excel training such as private instructors, videos, classes and more. Most computer programs offer a step by step tutorial that shows you simply and briefly how the program works and how to install it but the features and functions that it shows you are limited. In order to use a program effectively you need to know all the features not just a few.
Private tutors can cost a lot of money but if you really need the help to understand the program then it is worth it and you can get the attention that you need so if you don’t understand a specific thing or function then you can ask and get one on one attention.
There are a variety of online products ranging from videos, online classes and DVD’s if you don’t have much time to go somewhere and need something more catered to your bust life then chances are an online program is the right one for your situation.
There are many advantages and disadvantages to training in Excel such as learning the program, cost and more it is up to you as the consumer to decide which program best suits your needs.
A Microsoft Excel workbook is really a container, a bit like a folder. Each Excel workbook contains one or more worksheets and it is the worksheet that is the actual container of your information. Worksheets are identified by a tab which carries the name of the sheet. Clicking a tab will activate that particular sheet.
In exactly the same way that Microsoft Excel allows you to hide columns, it is also possible to hide entire worksheets. Hiding a worksheet is particularly useful where you have a workbook that contains a lot of sheets. Naturally, hidden worksheets can be made visible again by using the Unhide command. It is possible to hide either an individual sheet or to hide a group of sheets. However sheets can only be unhidden one sheet at a time.
To hide a single sheet, just right-click on the sheet tab and choose Hide. The corresponding worksheet will then vanish. There is also a ribbon command which achieves the same thing. First, select the sheet by clicking on its tab and then, in the Cells section of the Home Tab of Excel Ribbon, choose Format-Visibility-Hide and Unhide-Hide.
To hide more than one sheet at a time, simply select the sheets by clicking on the first, holding down the Control key on your keyboard and clicking on each of the others. Next, right-click on any of the highlighted sheet tabs and choose Hide.
To make a hidden worksheet visible once more, you can right-click on any sheet tab and choose Unhide. The Unhide dialog will then appear. Unfortunately, it is not possible to select more than one sheet to unhide; if you try Control-click or Shift-click, you’ll soon find that only one sheet can be highlighted. Highlight the name of the sheet that you wish to make visible and click OK.
If you prefer, you can also use the Excel Ribbon command Format-Visibility-Hide and Unhide-UnHide Sheet. When the Unhide dialog box appears, highlight the sheet you would like to unhide and click OK. You will notice that when sheets are unhidden they very conveniently return to the position that they originally occupied.
If you would like to learn more about Excel training courses, visit Macresource Computer Training, an independent computer training company offering Excel training courses in London and throughout the UK.
Charts offer a quick and easy way of graphically illustrating trends within your data. One glance at a chart can make it very plain where there is a dip in sales figures, a surge in visitor numbers and a host of other trends in whatever data is being represented. In this article we will examine the various elements of an Excel chart.
The first requirement is a set of data which can easily be converted into a readable chart. It is normally best to plot data which is a summary of your information. It is also useful if your data is arranged in columns or rows with headings at the top of columns or on the left of rows.
An typical example of information which would be easy to convert into a chart is a selection containing two columns with data on the left and the corresponding values on the right. When the chart is generated, the labels are placed on what is variously known as the category axis, horizontal axis or x axis; while values are arranged on the y axis. When your data is arranged in this format, the chart that Excel plots will not need much modification.
Charts may either be embedded or standalone. Embedded charts are created directly on the worksheet, often alongside the data being plotted. A stand-alone chart has an Excel sheet dedicated solely to the chart. This is known as a chart sheet; in contrast to a worksheet.
Whether embedded or standalone, the key components of the chart are always the same. First of all, we have a chart area. This is the background to the chart as a whole. Next, we have the plot area. This is the area where the graph or chart is actually plotted. Then, as we have seen, there are two or more axes. In a typical, “no frills” chart, there are two axes: the horizontal, or category, axis and the vertical, or value, axis.
Next, we have one or more series of data. In the example given above, where we select a column of labels and one column of values, there would be only one series of data. In a chart containing more than one series, it is necessary to clarify what each column represents. This is done by adding a legend to the chart. The legend acts as a key which tells us what each colour within the chart actually stands for.
As well as the text labels associated with the axes and with the legend, we can also include chart titles. In addition to the main chart title, we also have the option of placing titles on the axes. Within the plot area, we can also choose to display grid lines. These make it easy to read the value associated with each point on the chart.
These then are the main components within a chart. However, Excel allows you to customise each of these components and add other elements which enable you to create charts which convey exactly the message you have in mind.
When writing code in Microsoft Excel Visual Basic for Applications (VBA), you need to programmatically manipulate the objects inherent in Excel, such as worksheets, charts and pivot tables. In VBA, each of these objects has properties and methods which can be used to manipulate the object and make it do what you need it to. Properties are the attributes or characteristics that the object possesses, whereas methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.
Methods are a little more involved than properties in that they normally, though not always, require amplification in order to get a particular object to perform a particular action. This amplification is provided through the arguments which the programmer has to supply when using the method. Thus, in order to exit Excel altogether, we would use the “quit” method of the “Application” object, thus: “Application.Quit” without supply any arguments.
If, by contrast, we want to open a workbook, we need to supply at least one parameter: a string specifying the workbook to be opened. As you write your code, Excel will furnish a useful prompt: having entered the method, if you type an opening parenthesis, a “QuickInfo” tool tip will appear with a list of the parameters required by the method. This facility is much the same as the display of parameters when you enter a function in Excel.
Any arguments which are displayed in square brackets are optional while those not in square brackets are obligatory. Thus, for example, when using the “Open” method of the “Workbook” object, the “Filename” parameter is obligatory while the “ReadOnly” parameter is optional.
The “QuickInfo” tool tip lists all of the parameters, separated by commas, which the method can accept in the order that they must be supplied. If you do not wish to supply a given (optional) parameter, then you must still insert a comma to mark the position of the omitted argument. Thus, for example, if you wanted to use the “Open” method of the “Workbook” object and supply the “Filename” and the “ReadOnly” parameters, you would type “Workbooks.Open(”c:\reports\main.xlsx”, , True)”. Since the “ReadOnly” parameter is the third, a comma is inserted to mark the position of the missing second parameter.
There is another, very handy, technique for entering arguments is to enter the name of each parameter followed by “:=”. Using this technique, the order of parameters becomes unimportant and no reference has to be made to omitted parameters. Thus, in the example above, we could type “Workbooks.Open(FilePath:=”c:\reports\main.xlsx”, ReadOnly:=True)”.
The use of variables is common to all programming languages and are named areas of memory in which you can store data required by your program for its execution. To create a variable, you declare it, in other words, you notify Visual Basic of your intention to use a variable of a given name to store a given type of data. The keyword “Dim” (short for “Dimension”) is used to declare variables; thus, to declare that you will be using a variable called “strDepartment” to hold string (text) data, you would write “Dim strDept as String”.
VBA does not insist that you declare the variable type when declaring a variable; but it is useful to do so, since this prevents you from accidentally placing the wrong type of data into the variable. The “String” data type is used to hold text. For whole numbers, the “Integer” (small whole numbers) and “Long” (large whole numbers) are used. For real numbers (with decimals) “Single” (large real numbers) and “Double” (very large real numbers) are used. There is also the “Currency” data type which is used for large real numbers with exactly four decimal places. Then we have “Boolean”, a data type which always returns true or false and “Date”.
These then are the primitive (proper) data types, Excel VBA also allows you to place Excel objects into variables. Thus, if you want to perform a number of operations on a given worksheet, you could put a reference to that worksheet into a variable and manipulate the worksheet to your heart’s content simply by using the name of the variable. To declare the worksheet variable, you would use a statement like “Dim wks As Worksheet”. Then, to place a reference to a particular worksheet into your variable, you would use a statement like: “set wks = ActiveWorkbook.Worksheets(”Data”)”.
The scope of a variable is determined by the position in which you declare it: if you declare a variable inside a sub routine, then it will be local to that sub routine and the data it contains can only be used inside that sub. If you declare it at the top of the module, above all of the subs, then it can be accessed by all the subs within the module.
If your Excel VBA application contains several modules and you want all modules to share certain variables, you can also make variables global. Global variables need to be declared at the top of any module but they use the keyword “Public” instead of “Dim”. Thus to declare a global date variable called “dtStartDate”, you would enter “Public dtStartDate As Date” at the top of one of your code modules.
You can find out more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA Classes at their central London training centre.
Pretty much everyone who uses a computer know Microsoft Excel to some degree and most are aware of macros and VBA. However, the vast majority shy away from VBA assuming it to be the preserve of Excel gurus and professional programmers. Nothing could be further from the truth: having Excel VBA training is a natural progression for anyone who knows Excel well and want to take their knowledge to the next level.
It is important to stress, however, that it’s no good attending Excel VBA training classes if your knowledge of Excel is poor. You need to know Excel itself very well before you undertake any form of Visual Basic excel training; otherwise, the applications you create are bound to be flawed.
The art of developing effective Excel applications is to create functionality which complements the features built in to the program. Excel applications created by people who are not Excel specialists or simply do not know the program very well tend to perform actions which could be accomplished by using Excel’s own set of features.
However, if you already know Excel very well and you can spare the time, attending Excel VBA courses may be a very rewarding experience. You will discover that Excel VBA is not terribly difficult to learn and that there is a great variety of learning materials available. After all, Excel has been around for a couple of decades and it is installed on most business PCs.
One of the main considerations which make Excel VBA worth learning is that the applications you write never have to be built from the ground up. All you are doing is leveraging and automating the powerful capabilities inherent in the program. It’s a bit like walking on those long moving walkways that you get at airports. Any effort you put into walking is immediately amplified and you move much faster than you do when walking on static ground.
If you end up becoming serious about developing applications, you will have a guaranteed audience; since there are so many people using Excel. Finding a decent Excel VBA training course can help your career or even help you get a pay rise in your current job. The skills you will learn will stand you in good stead for years to come.
In this tutorial will look at the creation and customisation of a column chart. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.
The next step is to click on the Insert ribbon tab and from the Column drop-down menu choose the option that we require. The very first option is the omnipresent scattered column chart. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.
Having chosen a chart, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any member of a series to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.
There are a number of subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.
In the Stacked Column type, the overall total of all series within each category takes precedence over the individual value associated with each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.
Excel also offers us 3-D versions of these three different column chart types. However, these are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.
If you are going to successfully automate Excel, you have to refer to the various components and features which the program contains. In order to do this successfully, you need to know the name VBA name assigned to each element and the correct syntax to use in order to accomplish a certain task. Each component which forms part of Excel is an object with a specific name, usually a fairly obvious name, such as workbook, worksheet or range.
Excel offers a kind of dictionary to check the correct syntax to use when working with Excel objects; it’s called the Object Browser. Simply choose View – Object Browser in the Visual Basic Editor. Choose “Excel” from the drop-down menu in the top left of the Object Browser window which is initially set to “All Libraries”. The Object browser will then display a list of all the objects within Excel. Clicking on the name of an object will display the members relating to it; in other words, the syntax which can be used when working with that particular object.
The chief elements of syntax used to manipulate Excel objects are properties and methods. Properties are attributes which the object possesses while methods are actions which can be performed on the object. If we compare this syntax to English grammar, you could say that properties are like nouns and that methods resemble verbs.
Many properties of an object will be read-only; you can check to see what they are but you cannot change them. For example, we could check the version of Excel being used with the syntax “Application.Version”; but we could not set the version. By contrast, we can both read and alter other properties. Thus we could verify the users preferred number of sheets in each new workbook with the code “Application.SheetsInNewWorkbook” and we can alter alter this number with the statement “Application.SheetsInNewWorkbook = 12″, for example. Properties which can be modified in this way are referred to as read/write.
When setting object properties, it is important to provide a value of the correct data type. Thus, in the example above, an integer has to be supplied. Some Excel VBA objects also have properties which require an enumeration, one of a fixed number of set keywords beginning with “xl”. For example, if you want to set the location of a chart, you would use one of the three built-in “xlChartLocation” enumerations: “xlLocationAsNewSheet”, “xlLocationAsObject”, or “xlAutomatic”.
If you want more information Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA Classes at their central London training centre.