How to Create Dynamic Named Ranges

Author Zaheer    Category Guides     Tags ,

Dynamic named ranges are one of the strongest aspects of Excel. It is commonly used in listing the controls, PivotTables, and charts. First thin is to formulate a list in column of A of your worksheet.

Before anything else, there are still problems that could surface such as the updating or redefining of the named ranges every time there is an update on the records on the table.

Before creating dynamic named ranges, you must first settle with the OFFSET functions. This is the cell where you will be anchoring first and everything will eventually in relation to this cell. Most of the time, it is advised to make the header of the field be first on your record.

Next is to decide on the number of rows which is to be moved origination from the anchor address to start the range. In creating dynamic named ranges, it is advisable to start at 0 (zero) on the header to automatically begin at 1 (one). After, designate or decide on the number of columns which will be moved from the anchor address to start with the range.

After deciding on the number of columns, the number of extended rows where the range will be extended must be decided. Instead of hardcoding a value, a solution would be the substitution of CAOUNTA function for the selected primary or first field. In this manner, if you have decided or planning to add or delete items on certain fields the range will automatically grow or shrink in accordance to the changes. Also, you may need to deduct 1 from the result of the CAOUNTA in relation to the elimination of a row in the header. It is also required that you do not have any surplus data on the table range you are planning to evaluate.

After the above-mentioned, it is now safe to decide on the number of columns you will be using in your dynamic range. Take heed that the dynamic named ranges is dependent on a workbook’s capacity to calculate.

Also, another method to create dynamic named ranges is to open a new worksheet and enter your data. Click on the Formulas tab and in the Defined Names group click on the Name Manager. After, choose and click on New and in the New box type the Date. Click on the Refers to dialogue box and enter the formula.

After inputting the formula, click on New and type on the Name box the label. Again, click on the Refers to box ad input the formula after which hit Close. The formula being referred to uses the volatile RAND function.

Also, you can create names in Excel that could refer to the cell itself, the range of the cells, the constant values of the cell, of the formula itself. After giving names to the cell in the Excel, you can now use those names in creating or using formulas in replacement of certain values and other references. These names can also be used for navigation purposes to easily select the named cells and its range. To easily create an Excel with name ranged input in the Excel Name box the name. After inputting, select the specific cell pr cells to be named click the Excel name box, input a one-word name on the list and hit enter.

After making the Excel names which will eventually refer to the range you can now select an Excel name in the Name Box dropdown list. Also, you can use the Excel names for formulas.

Likewise, you can create a dynamic named range this is defined as when an item is added the range will automatically expand. To create a dynamic named ranged select and click on Insert then select Name and then click on Define. After selecting Define type on the name for the chosen range. In the Refers To dialogue box, enter an Offset formula that could define the range basing on the number of values in the column you have selected and click on Ok.

There are also available online tutorials for Excel Dynamic Ranged Named in using the range in formula. Also, there are tutorials in the sole creation of the Dynamic Ranged Named.

How to Use an Array

Author Zaheer    Category Guides     Tags ,

A variable which has is a similar type but holds a different value is what an array means. It is used in almost every programming language which can also be mixed or paired to different variables for a simple or more complicated structure of data.

Due to the property of an array that it holds different value it helps the programmer in a manner having only one reference. Also, it is safe to say that an array could be compared to a box with many divisions which can hold different data, such as numbers, letters, and other characters. On this note, it is also easy to say that an array needs more memory because it holds a lot of data.

Also, an array is used in sorting. In the gsort function, an array, a comparison function, the length of array, and the size of every element is required.

In using an array, you can pass the size of the array as a boundary if you already know beforehand the number of items the array will contain or is containing. You can use the var aColors or the new Array in generating the array within the document of the bracket. Also, it is important to understand and learn that an array with dynamism grows intently in size when an item is added in the Array. It is also needed to specify the specific values in creating an Array. Also, in creating an Array it must be equal to the items of arguments.

Now, in accessing certain items it is advised to use brackets wherein the position of the item being retrieved is enclosed. After enclosing it to brackets, you have to determine the amount or size of an Array with the corresponding length property. Likewise, the length of the property must contain one more position in the last item. Wherein, the Array which contains three items has items in the position of 0 all the way through 2.

If you would like to add another item in the Array which was previously defined, you can immediately add the value next to the open position. You can easily add because the Array automatically row or shrink.

Next, place a value in the certain position, example position 25 in an Array and fill in the position from 3 to 24. Fill it with the null values and place the proper value in the 25 position which will increase the Array size to 26. Lastly, define the Array of an object by the use of the literal representation. This literal representation is indicated by the use of brackets and the values will be separated with commas.

Also, you can do task and formulas in Excel which is in higher process. But before doing so, you must learn to master the Excel formulas and the usage of array formulas. These array formulas can be used in counting how many numbers or characters in the cells or range of cells. Also, it can be very useful in summation of certain or specific numbers that is in the same criteria. These criteria could be the smallest value in a range or the like. Likewise, in the array formula you can have the summation of all nth variables in a certain range.

In Excel, items in an array can be found in a single row, a column, or in multiple or different rows and columns. The downside here is that you are not allowed to create three-dimensional formulas of arrays in Excel. In this case, it is still the same that an array MS Excel formula can do tasks of multiple calculations in one or more items in an array. There can be single or multiple results. In Excel, multi-cell and single-cell formulas exist.

Top of create a multi-cell array formula in Excel, open a blank document and input numerical data and label it. Now, supposed you are to multiply the data or values in column and row C2 to D11. Select the cell where the answer will be located and enter the formula =C2:C11*D2:D11 and hit Ctl+Shift+Enter.

To create a single-cell array formula, input data or values in a blank workbook. Say for example you want to know the total sales of column and row C2 to C11 and D2 to D11. On column and row D2 input the formula =SUM(C2:C11*D2:D11) and hit Ctrl+Shift+Enter. The result will be the grand total of the columns.

How to Embed 8 If Statements into 1

Author Zaheer    Category Guides     Tags ,

MS Excel If function is very useful in the decision making process while you are considering your spreadsheets. It helps in generating and in testing if the data or the state of your spreadsheet is true or false. The function will carry out an action if the condition or state of your spreadsheet is true or false. If the condition is true then the function will carry out the specific task you have embedded on the function. These actions can be the execution of specific formulas, insertion of certain text or statements, or to leave the cell blank.

In order for the IF function to it needs to have a comparison for it to function. The most common comparison of IF function is the greater or leer than values. These symbols >/< is commonly used to create expressions which means a number is greater than a number, or vice versa. This expression can be true or false which will depend greatly on the IF function for the action to be done.

There are three basic parts of an IF function in Excel. These parts are the comparison function, what action will be taken if the function is true, and what action will be taken if the function is false. Also, there is a built-in wizard that could help you in making your own IF function Excel. To go to the function select on Insert menu and choose Function. After, click on the Logical function from the drop-down selection. Now, click-on the IF function and hit OK.

On the Function Arguments dialogue box, input on the Logical test box, Value if true, and Value if false your arguments or the functions for you formula. Right after inputting the IF functions, click OK. Logical test answers what you are asking. Example of a logical test question could be “Is the value in Column C greater than the value in Column D?” In the Value if true asks the question what you want to display if the answer to the question is true. In the Value of false it answers the question what you want to display if the answer is false.

To create IF statements the arguments must be separated by commas, an example would be =IF (A1>B2,”yes”,”no”) formula and place it on the C3 cell. This means that IF the value of A1 is greater than that of the value of B2 the C3 cell will show a yes answer and if the result is opposite the answer on the cell would be no. Remember that if you want the text to be displayed on the cell you must include the text with the quotation marks on the formula itself. On the other hand, if you will be using numbers then you do not have to enclose it with quotation marks.

You can also use the IF statements in assessing your workers performances, such as in deciding whether to fire them or give them a raise. You can enclose on the quotation marks, such as the one above with the words fired or rise. There are definitely numerous ways to make use of IF statements.

You can also repeat the IF statements or formula on the rest of the rows or columns. In case the IF statement is on the C2 cell then all you have to do is to point on the C2 cell on its corner. Wait until a black cross appears and then drag it down or right to left, depending on the cells you want to contain the same arguments or statements. Also, you can do the simpler way such as point on the corner of the cell and double click to have the same statements on the succeeding columns. Likewise, if you are to update the first or source cell of the statement you can do that and do the same process to have an update on all the cells.

The IF function or statements can be useful in Excel 2007, Excel 2003, and even for Excel 2000 version. Also, the maximum IF function of seven can be nested on the arguments for it to elaborately create and construct tests. If you are planning or aiming to do more than seven statements you can consider using LOOKHUP, VLOOKUP, and HLOOKUP functions.

 

How to Edit a Recorded Macro?

Author Zaheer    Category Guides     Tags ,

Even if you recorded your Excel macros perfectly and they have been very helpful with your work, you may still have to modify them. Imagine having more data to add to the reports. Imagine your company expanding. There are a million ways that would want you to edit the wonderful macros you had created. And of course expanding and excelling is a good thing, so bear with it!

The good part is that you do not have to create the entire record again. You can modify the existing one. The bad part is, in order to make the modifications and additions; you need Visual Basic Applications which is another program. Luckily, the program is no rocket science. You just need to understand some basic stuff and can get it installed. Once you do that, life will be much, much easier.

Here is how you edit a macro created already in your computer.

  • First go to the “start” menu and find Microsoft Excel. Open and through “file” open a new spreadsheet.
  • On the toolbar at the top of the new spreadsheet, find “window”. Click here and go to “unhide”. This would bring your personal macro workbook forward. You need to unhide the workbook to make the intended changes to the data.
  • To open the workbook you will have to click on “personal. XLS”. Click OK.
  • To access the created records, go to “tools” and select “macro”; go to “macros” and the records in the personal workbook will be listed. From here you can choose the one you want to modify and it would open when you chose to edit it.
  • Here is when the Visual Basic program comes in.

The language for visual basic program is not very different from the English syntax, as we have nouns and adjectives. In Visual Basic Application, the nouns are termed as “objects” and their qualities which would be the adjectives in common English or any other language are called the “properties” of that object. So, if you need that an object should have a desired property. You type the name of the object, followed by the property with a period between the two: object.property. Likewise, when you want one of the objects to follow a “method”, type in the name of the object, period, and then the method. It is simple but you need to understand these basics so that it does not hinder the editing rather than aiding it. Also note that when the Visual Basic Application is run, the names that you entered when you first created the macro will have additional codes assigned by the program. This is what you have to do. Copy the code, no matter whether or not you understand the code itself, and paste it five times in the column. To save the changes, go to file and click on Save Personal.XLS. Now go to “file” again and click “Close”. Now you can have the files and the Visual Basic Application closed down and the changed data will be saved in the same storage directory/ personal macro workbook.

This is all there is to editing the macro record. Once you are done with the above series of steps, you have successfully edited the macro. Now you should go back and double check if the method is working or not. Launch Microsoft excel again, and go to “file” and “new”. From here, if you remember the shortcut key you had specified earlier, press ctrl key followed by the shortcut code which is usually a letter or a symbol. The macro record will open and you can see if whether or not it is the edited version. The new names will also show in addition to the old ones.

In case you do not remember the shortcut key, go to “tools” and select “macro”, from here, click “macros” and from the list chose the one you want to double check. Click “run” and the corrected or edited version will be presented in case the editing was successful.

Skills like these require you to master them through sufficient practice. Once you do that, editing would not be difficult at all. So move ahead with the ease your computer has provided you!

How to Record a Macro

Author Zaheer    Category Guides     Tags ,

Tired of repeating operations while managing data? It is very understandable. Repeating the very same mathematical operation on all the numbers in s data sheet can be very frustrating.  Excel spreadsheets can be managed wonderfully, repeating tasks and getting frustrated can be successfully prevented in Microsoft Excel using Macro. Now here is how you record and use a macro and manage data without making your life miserable. The following steps are for recording simple macro in Microsoft Excel.

First open the Excel application. This should be simple. You go to the “start” menu on your screen and follow “all programs”. Find Microsoft Excel and click to open.

Now, go to “file” and then “new” to open a new spreadsheet, “save as” to keep the new workbook saved by a different name to avoid any confusion.

Find the “tools” button at the top menu bar of the new spread sheet and click. Here click “macro” following the temptation but avoid the “record macro” button for the time and go to “security” from there instead. Macro requires the use to set a security level. Ideally, you should set the security to medium. So. Click “medium” and it will be done. Click Okay to finish off this part of the business. The menu would close.

Now, go to the favorite button again. “Tools” menu followed by Macro again would show you a button “record macro”. Now you will be asked to enter the desired name:  you could have your company’s name or initials. Note, however, that the initial character has to be a letter and the rest is up to you: you can sue letters or numbers and signs if you please. You cannot have spaces between characters though.

In the “Shortcut key” section, you would be required to type in a shortcut symbol to use to stimulate macro immediately. This could be the first character of the name you selected for the application.

You would now be required to specify where you want to place the file. In the “store macro in” option you will have to select the destination. For an example, consider “store in… personal macro workbook”.

Find the descriptions section below and briefly describe the macro. After this you can save and close simply by clicking OK.

The recording formally comes now. A tiny window would pop up and it would show a blue icon. Whenever you want to stop recording, you can press the blue icon.

Here you would start entering say the titles related to the name of the managers in column A, first row. As soon as you are done entering the names, you need to click the blue icon to stop.

You can double check the recording now because once you have completed the above steps correctly, you are done. For double checking open a fresh Microsoft Excel window and press the keys Ctrl + (the selected shortcut code) and you will be directed to the macro you had recorded. Make sure you remember the code for shortcut to make the job easier.

As an alternative you can go to Microsoft Excel, and select tools, and then click on macro, and ten macros, from where you will have to select a directory or store where you had placed the record. Let’s just say, in the “macros” section you will find “personal macro workbook” and if you click there, you will have the list of records that you had stored in that folder. Of course the shortcut method is easier, but this is the longer route and requires you to know where the desired record has been saved by you.

These are the series of simple steps you need to follow in order to record a simple macro. In fact, when you learn this it is a very good idea to practice it and get the hang of it. This is so because as you excel, literally, you will need to record more complex macros and that would require a strong base. For complex records, there are complex techniques and procedures to record them. Gradually, you will see how the system works for you and it is not going to be a big deal at all by the end of the day. Learning afterwards can be facilitated by prior practice so go for it!

How to Create a PowerPivot?

Author Zaheer    Category Guides     Tags ,

Through PowerPivot service application you give meaning to your data recorded in Excel sheets. With PowerPivot you can stay connected as a group and get help in running your business smoothly and keep an eye on the progress throughout.  Creating a PowerPivot service application for your Excel data is simple and easy. Here are some simple instructions along with some every important tips that you could use to create and configure the service application. However, keep in mind that creating multiple service application would require you to fulfill what the system needs. So, in case you do not need the multiple applications, it is okay to avoid creating them as you would be required to put in effort otherwise.

  • Go to application management menu.
  • Find the “manage service applications” in the central administration and click there.
  • Here, you will find the Service Applications ribbon. Click New in this ribbon.
  • If the PowerPivot software is correctly installed, you will now see a SQL Server PowerPivot Service. You have to click here to find the “create new PowerPivot service application”.
  •  It is here that you will be required to enter the name of the application. By default the name is PowerPivotServiceApplication<number>. The number would obviously be provided to you. This may not apply if you go for multiple service applications: in that case you would need to give a descriptive name so that the administrators can understand the applications better.
  • It is recommendable to go to the application pool and create your own application pool with a managed account. The domain user account has to be specified so make sure you do not skip this part because it would enable to update passwords and other such information much more conveniently as you would now be able to use SharePoint’s additional features for managing accounts.
  • Note that in Database Server the databases are configured by the SQL Server Database Engine b default. You can stick to it or change it replacing it by another SQL server that you find more suitable to your interests and use.
  • It is desirable to choose a database name that is unique rather than having the default set name continued. This would make your work more recognizable and for yourself a lot easier to manage.
  • Then comes the database authentication. By default, it is windows authentication that is set. You can change it to another SQL authentication as you please. Again for this purpose, having an access to SharePoint’s features is very helpful.
  • Finally stay connected by choosing the checkbox for service connection. It is particularly important to select the checkbox if it is the first time that you are creating the application.
  • Finally, click “OK” and you will see the newly created application among the already existing ones, ready to be used.

You would now need to understand how to configure the PowerPivot service application and also need to learn how to connect it to a web application for better connectivity. For configuring the service application, you again have to proceed through the Central Administrator and follow the series of simple steps. The process is to an extent self explanatory and user friendly.  The major things you need to look at include the timeout and the pool size. Make sure you are well aware of the database load timeout and the configuration pool timeout. The pool size in question would be the maximum user pool size and the pool size for the maximum administrative connection.

Likewise, for connecting the PowerPivot service applications to a network you follow the same starting point: the Central Administration. Again you are lead to the default settings which you have a liberty to change in accordance with your needs and priorities. The main step is dealing with the editing of group associations as of course this is what connectivity is mostly about.

Fortunately, through the Central administrator you can edit the settings of the PowerPivot service applications anytime you want. This gives you enough chance to suit your needs and improve the system according to your business requirements. This is how easy to use the application is. So get started today.

How to Use Slicers

Author Zaheer    Category Guides     Tags ,

Dealing with massive data sheets on excel can be really messy. Particularly when you want to select some values for a process and skip other entries, the job can be really frustrating. In a long spreadsheet, where the entries to be skipped from an operation are spread in a random fashion, a slicer can be of great use to you.

It is not difficult at all to use slicers in Microsoft excel. Slicers have a set of buttons that allow you to filter the data in the Power Pivot tables. With the Slicers, you do not need to open individual lists and in the drop down look for the exact item that you want to filter out. Slicers hence make it simple and easy to manage your data filtering in Power Pivot, Microsoft Excel. In addition there is a clear labeling on the slicer that shows how the data has been filtered. This features helps you see which items have been left out and how the filtering process has been done.

While using a regular slicer to filter multiple entries, you cannot see the details of filtered items. Instead the slicer displays only that the data has been filtered. It does not show which items are left out and by what rule. To see the details you would have to drop the menu down and see the residual items. Like many programs there are slicers of different levels that operate on multiple levels of complexity. So, you can go for the one that fulfils the requirements for your job and tasks.

Typically, the slicers used in Power Pivot are associated with it since the beginning. Still, it is possible to sue an independent or online slicer and then attach it to the Power Pivot in your computer as you work on it. Most people use the slicers already attached to Power Pivot. You have a choice to suit yourself.

A typical Power Pivot slicer has the following set of buttons:

a-       A slicer header: this is an indication of the categories if the items being dealt with.

b-      A filtering button: this is not selected and shows the items that are not being filtered.

c-       Another filtering button: this is selected and shows the items included in the filter

d-      A clear button: by clearing you remove the filter from the data

e-      The scroll bar and border moving buttons allow you to fix the visible dimensions on the screen for your use.

Using tips:

In order to sue the slicers in Microsoft Excel, make sure you have a created a slicer and it is attached to Power Pivot; also you should have made a copy of the slicer and have it associated too. Use a slicer that already exists and attach it to another Power Pivot. As mentioned you can also create a separate slicer and then attach the stand alone to Power Pivot for further usage of the filtering component. Also note that each slicer you create is specified for one Power Pivot page, and for a different file, you would have to create a different slicer and get it attached to that particular file. Cumbersome in the beginning it might be, but as you get the hang of it, you would realize the effort is completely worth it. Luckily, in some cases you can share the slicers. This can be done when in a single report you have a number of Power Pivots. In such a scenario, you can share a filter for all of

Formatting Slicers

Working with slicers does not have to be dry and boring against the stereotypical concept of numeral data and data sheets. You can format an existing slicer too, by following simple steps:

Click on the slicer that you want to give a different outlook. From here you can find the “slicers tools” option and click on the options button. You will be presented with some available styles and for further designs you can click “more” button given and access further styles for your slicer. On and off you can change the look and avoid the monotony which is very prominent in this kind of work. Enjoy as you excel on Microsoft Excel.

How to Set Cell Data types

Author Zaheer    Category Guides     Tags , ,

What you enter in the Microsoft Excel spreadsheets is read by the program as general data, text, or numeric data. But there are limitations to each, and you obviously have to suit your own needs and preferences while using Microsoft Excel. The cell data type can be interpreted by Microsoft in different ways: numeric, general or text. The interesting thing is that even if you enter numerals you can have the program read the data as text. But why would you want to do that?

As a fact, Microsoft Excel automatically fixes the number that we enter. This is much like Microsoft word correcting some simple errors as we continue to type. Likewise Excel modifies the numerals so that they make sense. For example if I type in 0210 so Excel would automatically eliminate the first zero since it is meaningless to have that character. Bu if I want to keep it, I would have to change some settings. What is required in that Microsoft does not read the data as numbers? Once it does not interpret the data as numeric, it would not be bothered about the number sequence and corrections.

The “types” function in Microsoft Excel 2007 is a very useful feature as it allows you to determine and set the cell data type. The data you need to have in an Excel spreadsheet can be all numbers or numbers and letter or maybe just letters. With the type feature you can easily form formulas as a formula consist of both numbers and letters usually. The syntax for the type function that you need to know in order to use is TYPE (value). Here the value can be any type of data entry like a number or text for example.

Now here is how value type is encoded in this way. The following numbers show the corresponding value types.

  • 1 is for numbers
  • 2 is for text
  • 4 is for logical value: which means that the content is in terms of a true or false
  • For an error value, the code is 16
  • Finally, for a range or array of contents, the value 64 is used.

Converting data types

If the data you receive has numbers written in the form of text you may want to change the data type to make the spreadsheet more bearable! By default the numeric entries are right aligned in the cells. So when you see numbers aligned to the left you can easily tell. At other moments you may want to change a numeric format so that Excel treats it as text. This may be so because if an entry is treated as a number you cannot add a preceding zero as it would vanish automatically. Now if you modify the system such that the numeric entries are being treated as text, first they would be left aligned, and then you know you can add numbers as you please and there is no automatic emission of any zero for example.

Now, here is how set the data type:

  • General to text: To change the data from general to text, you only need to add an apostrophe before the number. With an apostrophe Microsoft Excel will read the numeric entries that you then enter as text and you can change them as you please.
  • Text to general: To deal with a text data that you want to be read as general data, you only have to click in the cell and simply hit the Enter key. And you are done: your text data will now be read by Excel as a general data.

This is how you use the program feature and keep your data the way you want it to be presented. The data type is thus decided by you and Excel follows. Moreover, there are some special software programs that help managing the cell data type. The usage and installation is easy and you do not even have to know the simple codes and techniques to switch the cell data type in your Microsoft excel spreadsheets. As you find the task doable, you should proceed. Follow the above steps or get a good program installed that could help you define data types.

How to Link Chart Titles to Cells in the Spreadsheet

Author Zaheer    Category Guides     Tags ,

There are many features available in Microsoft Excel that could help you in making your presentations. One of the features is that you can make use of charts in presenting numerical data. There are a lot of choices of charts that you can use which includes column, line, pie, etc. Also, when you decide to change your data, the data of your chart will automatically be changed or updated. You do not need to refresh the workspace.

In inserting a chart, click on option on the Insert tab Chart group. Choose then the type of chart you feel convenient. The type of chart you will be using can also be presented in a 2 dimensional or 3 dimensional form. Sub-types of bars are also seen in the selection. Also, excel allows you to collaborate different charts in one worksheet.

Likewise, before you start anything with linking charts to cells first learn the basics. There are a lot of components in a chart. Some are already shown in a default chart while the other components can be added. First, the chart are refers to the entire chart. Next, the plot area is where the axis, title, and marks are seen and located. The next area is the data points where the variables are plotted and is now represented by a bar, line, pie etc. The data series connects the data points in the chart. The axis, horizontal and vertical contains the variables an the categories of the data. The box which contains the designated color or patterns for your data is called the legend. The one that contains the titles of the chart or charts is the title. Lastly, you have the data label which is used to classify the information regarding you variables.

In order for you to add a title on a current chart or an axis you have to select the chart sheet first and select on where you want the title to be placed. Select and click-on the Chart menu and select Chart Options. Take heed that the Chart menu will only be available if you are able to select an existing chart. Now, find the Titles tabs and enter the title which you think is right for the title box. If you wan to insert a line break on your existing chart title, just pint on the on the chart title and hit enter to begin editing. This is also the same if you want change the font style and font size of you current title. Also, if you want to format the current text, click on the Format menu and select Selected Chart Tittles or Selected Axis Title. Likewise, in changing the font click on Font tab and select on the format you prefer.

Now, on linking your current or existing chart or axis to a worksheet cell click first the chart sheet or the embedded chart. The chart sheet is the workbook that only has the chart. Having a separate sheet for your table is useful when you view the chart. Also, the embedded chart is the chart which you have on your worksheet together with the data. This will also be useful if you plan to print your data together with the table.

After, select on the formula bar and input an equal sign. The formula bar is where you get to input or change the variables and formulas in a selected cell. This is found at top of the workplace on the Excel. Also, you can enter a reference for you worksheet. This includes the name of the work sheet, then an exclamation point, and hit enter.

Also, linking chart title to worksheets could come very handy if you are using the same chart mostly or at the same time. This will automatically change your existing data which will make the two simultaneously relate. There are a lot of links you can have on your chart such as chart title, the X-axis and Y-axis title in secondary and primary form, and the different labels of data. Having charts on your worksheet is helpful in presenting your numerical data in large numbers be understood easily.

How to Create a Pie Chart

Author Zaheer    Category Guides     Tags ,

Microsoft Excel offers a lot of variety of charts you can use for your data to be presented easier and less complicated. But the excellent type of chart for you to display data points. It is also easier to distinguish sizes in a pie chart because these sizes are proportionate to the total of the items. It is also needed that in arranging the data on the worksheet must only be one column or row. There are also a lot of thins to be considered when you decide to use a pie chart in plotting data. First is that you must have one series of data to be plotted. Negative values and zero values could not be plotted on a pie chart. Also, a maximum of only seven categories can be plotted and that these seven categories are represented by the different parts on the pie chart.

There are also subtypes of a pie chart from which you can choose from. These types are the pie chart in 3-D, pie of pie charts or bar of pie, and exploded pie which can also be in3-D. The first type of pie chart can display the sizes of the data or values in 2-D or 3-D. Also, you can remove or move parts of the pie manually. You can also choose the pie of pie chart or the bar of pie chart which allows you to present extracted data from the main pie chart. This is very helpful in displaying parcel of the main pie chart for you to easily differentiate the values. The last subtype is the exploded pie chart emphasizes on the values of the individual parts and can also be in3-D. in this subtype, you cannot move the parts of the pie.

In creating a pie chart, first open you worksheet which contains the data you will be using for your pie chart. After opening, select the values you would want to plot or input on the pie chart. Select the Insert tab found on the top pf the workplace. Click on the Charts group and select the Pie. Select on the Pie in 3-D from the 3-D Pie. Now, click on the plot are. The plot area is where the axes, categories, labels, and all the data are located. The Chart Tools shows the selection on the Design, Layout, and Format tabs for the pie chart. Click on the Design tab, after click-on the Chart Layouts group and select the layout you prefer for your pie chart. There are different layouts you can choose from for you pie chart. Also, you can change and choose your design from the Chart Style group. After deciding on the design of your pie chart you can also change its formatting such as its shape styles and effects.

To be able to customize the display of data points on your pie chart first position the primary and secondary charts adjacent to one another. The primary pie chart is located at the left side and is larger than the secondary chart which is located at the right side. In changing the charts, you cannot do it separately because the values or data are interrelated. Also, the lines for connection are automatically connected but you are still allowed to change the default format of the line and even remove it if you like.

To be able to change the data series of the current pie chart first select the data series. Now, click on the Format menu and click-on Selected Data Series and click on the Options tab. To specifically have the data you want to see on the secondary chart select on the Split Series by selection.  If you want to change or adjust the number of data points which is shown on the secondary chart you can select on the Second plot contains the last values box for you to split the position of the series.

If you want to change the size of the secondary chart you can increase or decrease it by selecting the Size of second plot box. Also, you can remove the lines connecting the primary and secondary pie chart in the Series line check box. For you to be able to make the colors of the data points the same, select on the Vary colors by slice check box.