Change Price List In Tally

This option allows you to view the existing Price List based on Stock Groups or Stock Category in edit mode, here you are allowed to change the various information.

To alter a Price List based on Stock Groups,

Go to Gateway of Tally > Inventory Info. > Price List > Stock Groups

1. Select the required Stock Group Name from the List of Stock Groups.
2. Select the required Price Level from the List of Price Levels.
3. Change the Applicable From date, as required.

Follow the same procedure as discussed in Creating a Price List, to alter the Price Lists.

Note: Similarly you can alter the Price List based on Stock Category.

To view all the Stock Items under the selected Price Level, click on  F12: Configure and set the option Show all Items for Price Level to Yes.

Once you have created Price Levels, the Price List option appears in the Inventory Info. menu

Go to Gateway of Tally > Inventory Info. > Price List

Creating Price List based on Stock Group

Go to Gateway of Tally > Inventory Info. > Price List > Stock Groups

• If you want to create a Price List consisting of all stock Items falling under different Stock Groups, select Stock Groups from the Price List Menu.

• Select a Stock Group Name from the List of Stock Groups.

• Select the Price Levels from the list

Note: To view all the Stock Items under the selected Price Level, click on  F12: Configure and set the option Show all Items for Price Level to Yes.

• Type the applicable rates for the stock items for the selected Price Levels (you will notice that all the items grouped under Components appears under List of Items).

• Accept to save

Under Group

This is the stock group of the Stock Item, for which you want to define the Price Lists.

Price Level

This is the Price Level under which the Price List needs to be created

Applicable From

This is the date from which the defined Price List is applicable.

The columns you see are as follows.

Sl. No

This column is auto-generated serial numbers to track the number of items in the Price List.

Name of Item

This column contains the Name of the Stock Items under the selected Stock group. You can define the Price List for each item.

Quantities - From & Less Than

These fields are repeated for an item and begin with a blank for 0 items and end with a blank for any number of items (i.e., upto 50 Nos you can have a certain pricing if someone buys more than 50Nos you can specify a different rate). This is useful for quantity based pricing and discounts. You can create a staggered quantity price structure, if required.

If you do not require quantity based pricing, leave From and Less Than fields blank.

Rate

Under this column, you can specify Rate, the Rate for the item based on the quantity range given.

Discount % (if any)

You can define a discount percentage in the Price List which will be taken automatically in the sales invoice, you may also choose to maintain the same price for all bands, but define different discounts.

Historical Details (Rate and Disc%)

This column displays the last Price List specified for such Stock Group.

There are two columns: Rate and Disc %. Tally.ERP 9 provides the historical details of the sale rate and discount percentage of any previously created Price Lists. Therefore, you can define the current price details based on the information given in the Historical details.

Cost Price

In this column, Tally.ERP 9 displays the cost price of the item calculated based on the stock valuation method given in the item master. This has been provided to assist the user in giving the appropriate sale price. By viewing the historical price details of an item, we can accordingly define the sale price for the new Price List.

Note: For the discount percentage to be taken in the Price List, you should activate the feature Separate Discount Column on Invoices in the F11: Features (Inventory Features).

Buttons in Price Lists Screen

Ctrl+F4: New Parent

Select this option to change the parent Stock Group Name, Price Level from the List.

Creating Price List based on Stock Categories

Go to Gateway of Tally > Inventory Info. > Price List > Stock Categories

• If you want to create a Price List consisting of all stock Items falling under different Stock Categories, select Stock Categories from the Price List Menu.

• Select a Stock Category from the List of Stock Categories.

• Select the Price Levels from the list
• Type the applicable rates for the stock items for the selected Price Levels

Buttons in Price Lists Screen

Ctrl+F4: New Parent

Select this option to change the parent Stock Category Name, Price Level from the List.

Print/Export/Email/Upload Price Lists

Select the relevant buttons while the price list is displayed for it to be printed / sent by e-mail or published.

Excel 2007 Easy Learning - Step 16

In this tutorial we will be learning about the Excel 2007 data tab. This tab enables you to import data from other programs, update your spreadsheet when changes are made to the external data sources, sort, filter, and organize your data. Lets' get started with the first section, Get External Data.

You can import spreadsheet information into Excel 2007 from about any program. Excel 2006 gives you a button to import from Access, a Web site, or Text file. Click one of those buttons and a window will open for you to select the location of the data you want to import. If your data is in a source not listed click the down arrow on From Other Sources and make a selection. If you need help please email me and I will walk you through your specific question. The last button is Existing Connections. The benefit of using a data source connection is it can make it less time consuming to analyze data in Excel from other programs. Usually you would have to cut and paste data into Excel with the Data Source connections you don't have to do that.

Let's Move on to the Connections section of the data tab. The first button is Refresh All. If you have your spreadsheet connected to a data source and you make changes to your data source then you will need to update your spreadsheet. Click the Refresh button you will be given the option to update all data sources, update just the work book, check on the status of the refresh, or cancel the refresh.

The Connections button will open a window that gives you a list of all of your data connections a description of the connection, where the connection is located and the last time it was refreshed.

The Properties button will be grayed out until you have a data source connected to your spreadsheet. Properties with tell you how cells that are connected to a data source will be updated, what contents from the source will be displayed, and how changes in the number of rows or columns in the data source will be handled in the workbook.

The Edit Links button will open a window for you to view all other files the spreadsheet is linked to and how they are linked and it will allow you to make changes to the links.

The next section is Sort & Filter. If you have large amounts of data in your spreadsheet it is helpful to be able to organize it and find what you are looking for. To use any of the sort buttons you can click the top of the column or highlight all of the data you want sorted then click the button you would like to organize your data with. The AZ button with the down arrow will sort your data from lowest to highest. The ZA button with the up arrow will sort your data from highest to lowest.

The Sort button opens the Sort window which will give you more options when organizing your data. You can sort on multiple levels if you have several columns of data. For example you could sort my last name, then date, then amount owed. Make you selections then click the OK button to finish and your data will be sorted.

To use the filter button select the cell at the top of the column you want to filter then click the filter button. This will place an arrow in the cell. When you click the down arrow you will get a menu to make your filtering selections. In this example I am filtering a date column. It gives me the option to just show 2010 dates, all dates, etc. You can either check the boxes provided to filter or click the Date Filters option to have more choices such as filter by quarter, month dates past today, etc. Play with the options and when you are finished click ok for the filter to take place. To clear a filter click on the down arrow and select Clear filter From "column name" or click on the clear filter from the sort and filter section of the data tab.

The last button in the sort and filter section is the Advanced tab. This will open a window for you to customize your filtering more.

Next is the Data Tools section. The first button is Text to Columns. This button will separate words into 2 columns. To try this type you first and last name in one cell. Then click the Text to Columns button. It will open the Convert Text to Columns Wizard.

The first step of the wizard has you tell Excel how your words are separated in the cell. I need to click Fixed width because I just used a space. Then click next. In the next window lets you set the column widths. You can adjust the widths by moving the arrows. When finished click next for the third window. The third window has you set the type of cell format, ie: text, currency, etc. make your selection and click finish. It will return you to your spreadsheet with all of the selected cells separated into 2 cells.

The next button is Remove Duplicates. If you have columns in your spreadsheet that may have duplicate data you can click the Remove Duplicates button and the Remove Duplicates window will open.

In the Remove Duplicates window check the boxes next to the columns you would like the duplicate data removed. Then click OK and all duplicates will be deleted from the spreadsheet.

The next button in Data Tools is Data Validation. The Data Validation button prevents invalid data from being entered in cells. Click the down arrow on the Data Validation button to see the options then click on the Data Validation option. This will open the Data Validation window to the settings tab. In this tab you can customize the data that you will allow to be entered into the cells you have selected. The down arrow under Allow will let you make your selections. Some examples are decimal, whole numbers, date, time, text or you can customize. After making a selection The Data selections will no longer be grayed out. Use the drop down arrow to select the type of data allowed. Depending on the selection made will depend on the variables you will be allowed to enter. I selected between. This gave me a minimum and maximum text box to fill in. I typed 3.2 in the mininum and 5.7 in the maximum.

Now click on the Input Message tab. If the check box is marked it will let you customize a message that will appear when the cell with data validation is selected. The message will appear like a note. The title will be bold at the top and the message will appear below. If the check box is not marked there will be no message displayed to tell the user of the data valitation for the cell.

Click on the Error Alert tab. I would recommend always checking the box to show an error when invalid data is entered. It this box is not checked the user will not know why the data will not show up in the cell. Once the box is checked use the drop down arrow in the style section to choose the icon that will appear in a pop up window when invalid data is entered. Then type the title and message in the text boxes. Click Ok once you have entered the title and message. Now type a number that is invalid and press enter. You should get a window similar to this.

If you choose not to have an error alert to prevent invalid data from being entered you can have the data circled. Click the down arrow on the data Validation button in the drop down menu click the Circle invalid data option. You will need to go back and change you settings in the data validation window to allow invalid data to be entered. When the invalid data is entered a red circle will be placed around the cell. To clear the circles use the last option Clear Validation Circles.

Excel 2007 Easy Learning - Step 15

The Excel 2007 formulas tab is for more advanced users. I am going to go through this section quickly, but remember if you have specific questions about using formulas please email me.

The 1st section is the Function Library. This section contains buttons that will help you with all of the different formulas Excel 2007 provides.

In this section the first button is the Insert Function button. This button allows you to edit the formula in the cell that you have selected. Formulas are equations that perform calculations with numbers in the cells in your spreadsheet. A formula always starts with an equal sign (=). For example 4+2*5 will multiply 2 and 5 then add 4.

Auto Sum is a common function that even beginner users find helpful. If you click the down arrow on the Auto Sum button you will see a list of formulas you may find useful. To use these formulas click on the cell you want your answer to appear. (If the cell you choose is not directly below a list of numbers Excel will place the formula and let you highlight the numbers you want to use.) Then select the Auto Sum function you want to use; Sum will add your numbers, Average will provide an average to a list of numbers, count numbers will count how many numbers you have, Max will find the highest number in your list, and Min will find the lowest number. Once you click your formula your work is done and Excel will provide your answer.

I am going to go through the rest of the Functions quickly. Please email me if you have any questions.

• The Recently Used button will change as you use more functions. The down arrow will list your most commonly used formulas.
• The Financial button will give you a list of financial functions such as accrued interest.
• Logical will give you true or false results.
• Text functions can do things like capitalize the first letter in each word, find a word, or give you the phonetic characters from a string of text. This is just a few examples.
• The Date button will give you options to calculate the number of days between two dates, give you a serial number for today’s date, or will give you the number of work days. Again these are just a few options.
• Lookup & Reference can do things like create a hyperlink, reference a column or row, or create an index.
• The Math & Trig feature I used a lot when I was in college. This will help you with many of the formulas you are learning in advanced math classes.
• More Functions includes formulas for statics, engineering, and much more.

I went through this very fast so email me if you have specific questions and I will add them to the tutorial.

The next section in the Formulas tab is the Defined Names section. This section we will learn about finding names in your spreadsheets and defining cells with names instead of cell numbers.

Click on the first button Name Manager. This will open another window.

In the Name Manager window you can create, edit, delete and find all of the names in your workbooks. You can use the names in your formulas instead of cell references.

The Name Manger function is a great tool if you have several sheets, tables, and formulas in your spreadsheet. It makes it easier to memorize names rather than hunting for cell numbers.

The next buttons; Define Name, Use in Formula, and Create from Selection let you do the same thing as the name Manger button, except you do it with individual cells.

The Define Name button you select the cell you would like to name and click the Define Name button this will bring up a window for you to name the cell then you will not have to use the cell number in your formula.

For the Use in Formula button you select a cell that already has a formula and then click the Use in Formula button. It will give you a list of all the named cells in the work book to choose from.

The last button in this section is the Create from Selection. This button will automatically create names from a selected group of cell.

If you need more information on this topic please email me.

The next section in the Formula tab is Formula Auditing. I am going to go through these quickly to give you the basic idea of how they work and if you have specific questions please contact me.

The First button is Trace Precedents. To use this button click on a cell that has a formula then click the Trace Precedents button this will draw arrows to the cells that affect the results of the formula. To use the Trace Dependents button click on a cell that is associated with a formula. This will draw arrows to the formulas that the cell is associated with.

To remove the arrows from the Trace Precedents and Trace Dependents buttons click the Remove Arrows button. If you just want to remove one or the other click the down arrow for those selections. The next button is Show Formulas. Click this button and you will see the cell formula instead of the value or result. Click the button again to hide the formulas and see the results again.

The Error checking button will check for common errors made when creating a formula in Excel.

If you have a long formula the Evaluate Formula button can be helpful. It will open a window that will allow you to look at each part of the formula and help to find errors.

The Watch window is a neat feature. If you have several formulas in your spreadsheet and would like to see what happens to a series of formulas when you are changing cells in your spreadsheet. Click the Watch Window button. A window comes up. Click on a cell that has a formula and click the Add Watch button in the watch window. It will add the cell to the watch window. You can add as many cells as you like to this window and as you make changes to your spreadsheet you have a central location to look at the values of your formulas.

The next section in the formula tab is Calculation. Calculation Options button give you the choice of having values in a formula be calculated automatically when changes are made to cells that affect the formula, automatically change them except if the formula is in a table, or to have you manually update formulas by clicking either the calculate now button to update the calculations in the workbook you are working on or the Calculate sheet button to update the formulas in the sheet you are working on.

That wraps up the formulas tab tutorial. I know I went very fast through this but please email me if you have specific questions.

Excel 2007 Easy Learning - Step 14

The last section in the Page Layout tab is Arrange. Most of the arrange buttons are for objects you have inserted into your spreadsheet. The first button is Bring to Front. This button will be grayed out until you insert an object into your spreadsheet. You can review the Insert Tab tutorials to learn how to insert an object. Once you have an object inserted into your spreadsheet you can select it and click the Bring to Front button. This will place the object in front of everything so no part of it is hidden. The Send to Back button is just the reverse. Once you select your object click the Send to Back button and it will send the object behind everything.

The next button is Selection Pane. Click the Selection button and notice you get a pane on the right side of your spreadsheet.

The Pane will list all of the objects you have on your spreadsheet. You can move them front to back using the arrows at the bottom of the pane.

The Align button will let you align the edges of objects so you can have a uniform look. If you click the down arrow you can see all of your options. You can align the left edges, center the objects, align the right edges, align the top middle or bottom of objects.

You can also move the objects so they appear evenly across the page either horizontally or vertically.

The Snap to Grid options will only let you move the object to line up with the gridlines. You can view the Gridlines with the last option or create a shape and only be able to move the object within the shape.

To Group objects together click one object and hold down the Ctrl button and click the other objects. Then click the Group button. This will let you move and edit the objects as a whole. Once you have grouped items together you will have the option to ungroup them in the Group down arrow.

The last button is Rotate. If you click the object then the down arrow on the rotate button you have the options to flip the object over, rotate it 90 degrees clockwise or counter clockwise.

That concludes the Page Layout tab of Word 2007. Click the next button to continue to the Formulas tab or click one of the below links to try another tutorial.

Excel 2007 Easy Learning - Step 13

n the Excel 2007 Page Setup section of the page layout tab the first button is Margins. Click the down arrow to see the default selections. These are the margins that you need to set for printing your document.

You can also set custom margins by clicking the custom margins selection at the bottom of the Margins button.

The Page Setup window will open automatically to the Margins tab. In this tab you can set the top, bottom, left and right margins. In this tab you can also set the spacing for the header and footer or center the spreadsheet either horizontally or vertically on the page. The page in the center will give you a preview of how your spreadsheet will print.

The next button is Orientation. Click the down arrow on the Orientation button. This button lets you change the orientation of your spreadsheet to portrait or landscape. If you are following the Budget tutorial Change the spreadsheet to Landscape then click print preview to make sure the whole spreadsheet fits on the page. If it does not fit we will get to that soon.

The next button in the Page Layout tab, page setup section is Size. This button will let you select the size of the paper you will print your spreadsheet on. Printing small spreadsheets on note cards for school exams is where I found this option most valuable.

Click the down arrow to see all of the page size options.

The 8.5” x 11” paper size is the default selection. You can use the scroll bar on the right had side of the drop down menu to see more sizes or if you would like more options click the More Paper sizes option at the bottom of the list.

Moving right along to the Print Area button, click the down arrow and you will see 2 options, set print area and clear print area. Sometimes when creating a spreadsheet you may want to print on a certain section of the spreadsheet. To do this select the area of cells you would like to print and click the down arrow on the Print Area button, then select Set Print Area. You will see a dotted line around the area you selected. If you click the print preview button in the Microsoft logo button at the top of Excel 2007 you will only see the section you selected. All other cells will still be available for you to modify and use but they will not print.

To clear this print area select the cells again and click the clear print area selection.

After selecting print area you will get another option in the print area button. You will see an option to add to print area. Simply select more cells and click Add to Print Area and you then be able to see the newly selected cells in your print preview or when you print the spreadsheet.

The next button is Breaks. Click the down arrow in the Breaks button. This button lets you separate a long spreadsheet into multiple pages.

Once you have inserted the page breaks you can remove them by clicking the Remove Page Breaks.

Once you have inserted and removed page breaks the Reset All Page Breaks button will no longer be grayed out. This selection will reset you page to the way you saved it last.

The Background button is next. This button will open a Background window for you to browse through your folders for a background image for your spreadsheet. This image will be placed behind all of the cells and will not show through if you have cells that are colored.

The last button in the Page Setup section is the Print Titles button. Click this button and the Page Setup window will open to the Sheet tab. The Print titles options allow you to repeat rows or columns on each page when you print multiple page spreadsheets. To do this, click the icon at the far right next to the Rows to repeat at top. This will bring you back to your spreadsheet. Click and drag your mouse over the cells you want to print at the top of each page of the spreadsheet then press enter to return to the Sheet tab. It will place the formula for the cells you choose in the text box. Then click OK. Print your spreadsheet to see that each page you printed has the selected rows at the top.

Excel 2007 Easy Learning - Step 12

he Excel 2007 Page Layout tab in Excel lets you make changes to the overall page, including margins, colors, printing and views.

The first section in the Page Layout tab is Themes. Click the Themes button down arrow to see a list of themes you can choose from. The themes button will change your whole document design as far as color, font, and effects.

The other 3 buttons let you edit the specific aspect of the design of your spread sheet. To change the Colors in your Excel document click the down arrow on colors button.

 

If you click the Create New Theme colors at the bottom you will open the Create New Theme Colors window. This window gives you an idea of what each color in the row changes. You can also use this window to create a custom theme color for your spreadsheet by clicking the down arrow in each category and selecting a new color. To save your changes name your theme then click Save.

To exit without saving click Cancel.

To create a new theme font click the down arrow in the Fonts button. You will see a list of default themes you can choose from these themes will change the font for the heading and body text.

You can also click on the Create New Theme Fonts you customize you font theme.

The next button in Themes is theme effects. Theme effects are sets of lines and fill effects. You are not able to customize your own theme but when you place your mouse over one of the default themes you can see the changes in your document and when you are happy with one click on it and the changes will take effect.

Excel 2007 Easy Learning - Step 11

This section works a lot like the functions in Word 2007. The first button is Text box. If you don’t want to use the cells in your spreadsheet you can insert a Text Box. The text box will be place over the top of the cells and give you the opportunity to type and adjust the font styles without having to adjust any of your cells. Click the Text Box button to get an idea of what will happen. If you don’t want a text box you can always delete it when you are finished playing with the features.

The next button in the Text section is Header & Footer. Click this button to see how you create a header and footer on your document.

Notice that I now have a different view of my budget spreadsheet. I am going to enter the date for a header and page number for a footer. To do this click the current date button in the Header & Footer Elements section. Then click Go to Footer in the Navigation section. It will shift your view to the footer then click the Page Number button, which is the 1st button in the Header & Footer Elements section. When you are finished click any cell in your spreadsheet to return to the normal view.

The next button is Word Art. This button will allow you to insert decorative text into your spreadsheet. To insert Word Art click the down arrow and select a style. This will put in a box that looks like a text box in your spreadsheet on top of the cells. The Drawing Tools Format Tab will appear for you to edit your Word Art.

The next button is the Signature Line. This is useful if you would like to ad a line to get a signature from some one. To add a Signature Line click the down arrow and select Microsoft Office Signature Line. This will open the Signature Line window so you can fill in the name and title of the required signature. Click OK once you are finished. This will add a box on top of the cells that you can adjust the size and place where you want in the spreadsheet.

The next button is Object. If you would like to embed an object such as an Adobe File, Media Clip, or Word File into your spreadsheet you can do it with this button. If you Click the Object button the Object window will open. You can select the type of Object you would like to embed in your spreadsheet and click OK. Once you have embedded the object you can double click on the Object to edit or view. If you need help with this feature email me.

The last button is the Symbol Button. Click this button to see the Symbol window that it brings up.

The Symbol window allows you to select symbols that you cannot enter with your keyboard into your Excel cells. Select a symbol that you would like to try and click Insert. This will put the symbol in the cell you have selected. If you do not want the symbol there just hit the back button to delete it.

That wraps up the Insert tab for Excel 2007 Click the Next arrow to move onto Page Layout.

Excel 2007 Easy Learning - Step 10

Lets move onto the Links section. The Hyperlink button lets you link a cell in your spread sheet to a website, document, email address, or program. I am going to show you how to link my electricity cell to the website where I pay my bill. First click the cell you would like to create the hyperlink in then click the Hyperlink button and the Insert Hyperlink window will come up.

In the Insert Hyperlink window you have the option to create any type of link you want. The buttons on the left column allow you to select the type of link and the right hand options let you give the link details. To create my web link I will type my web address down in the Address section then click OK.

Notice that my Electricity cell is now blue. If I click once on the cell it will open the website where I pay my bill if I click and hold the mouse button down I can edit the cell.

Excel 2007 Easy Learning - Step 9

The next section is used to create charts. The options in the Charts section will let you create different styles of charts. Let me show you how to create a chart using the data I have entered for my budget spreadsheet.

I want my chart to be on a different sheet so I selected another sheet. To create my chart I am going to use the Column option. I clicked the down arrow on the column button and chose a cylinder style chart to compare my actual cost verses my budgeted costs.

Doing this placed my chart area in the center of my Chart tab. Now I need to click the select data button at the top in the Chart Tools.

The Select Data Source window will open. I clicked on my Budget tab then highlighted the cells I wanted to include in my table. Make sure you highlight your title so it will appear in the table.

Then click OK and your chart will be created.

That is the basics of creating a chart. Creating a chart works the same with all of the options you see in the chart section. The different types of charts can be used to provide various images for your data. If you would like more detail on creating charts please email me.

Excel 2007 Easy Learning - Step 8

Now we are on the last section of the Excel 2007 Home tab, Editing.

The first button in the Editing tab is the sum symbol. Click the down arrow to see the options.

In my budget spreadsheet I have put a total row at the bottom of each section. It is very easy with this button. All you need to do is click the sell below the numbers you would like to total, average, count, find the max, or min the click the down arrow of the sum symbol and make you selection. It will do the formula for you.

The next button is a down arrow. This will copy cells for you in any direction. The feature I like with this button is the Series selection. If you have a row of dates you don’t have to type each date. Just type the first 3 and highlight how far you want the dates to go then select Series. A window will come up and let you select the date unites you want to count by then select ok and the cells you selected will automatically be filled in with the dates.

This selection does not always have to be a date it can be counting by 2’s or what ever series of numbers you would like.

The next button in the Editing section is the Eraser. Click the down arrow to see the options. This feature lets you selectively erase parts of your spreadsheet. You can Clear everything, Clear only the formatting, Clear the Contents of selected cells, or if you have comments (we have not learned about these yet) you can just clear those.

The next button is the Sort button. This button will let you sort your data from highest to lowest depending on what type of data you have you can also alphabetize columns of data with the sort button. Notice in my budget spreadsheet I sorted my Household data from lowest to highest. You can also do this if you have created a table with the format table button it gives you the sort arrows above each column. If you have not created a table you will need to highlight the area you would like to sort.

You also have the option of doing a custom sort. This will open a new window for you to input your selections. If you need help with this email me.

The filter button is what gives you the arrows above each column. If you have not used the table feature you might want to filter a row. Try filtering a row and looking at the options the arrows in the cells give you.

The last button is Find & Select. This button works like a search. It will find specific numbers or words in your workbook. Click the down arrow to see the options.

If you have large amounts of data this is a quick way to find what you are looking for. I also find the Replace feature very useful. The Replace button will open another window and let you type the text you are looking for then the text you want to replace it with. It will go through the entire document and replace all matching text.

If you would like more details on this section please email me. The other features can get very advanced.

That wraps up Excel 2007 Home tab tutorial. I hope you learn what you wanted to know. Click the next arrow to move onto the Insert tab in Excel 2007.