Download an excel file that contains many examples of the features mentioned below
When importing files that have the file extension .txt or .csv or .prn, you will be required to choose one of 2 methods. You can use either the Delimited or the Fixed Width option. Excel is usually very good at determining which method to use. If you are not sure which method to use, use the one defaulted by Excel. If it is not quite right, you can always re-import the file.
If choosing the delimit option, the wizard defaults the choice of tabs as your delimiter. You can add and delete different delimiters. The columns are by default set to a General default. Dates should be changed to date formats and the drop-down box allows you to choose the order e.g. dmy, ymd, mdy etc. Click on the "finish" icon to conclude the import process.
With the fixed width option you can move a line by clicking and dragging, delete a line by double-clicking on it and insert a line by clicking once in the required position. The next step allows you to modify the formats. Click on the "finish" icon to conclude the import process.
Exporting Files from Excel
Files from Excel are generally saved with the usual .xls file extension. If you are wishing to save the data in a flatfile format e.g. .txt or .prn extension, Excel will prompt you with the message "xxxx.txt is not in Excel format. Do you want to save your changes". You have saved the last version of your file in a file format other than Excel and the program offers you the chance of saving it in an Excel file. Don't worry about this command - just click "No". Clicking cancel will leave you in Excel with the same file open - with .txt files you generally need to have closed the file.
Paste Special
This is a useful command, which allows you to paste data using additional features. If you use the normal paste function - this copies formulae, which are not always required. Once having selected the data you wish to copy, and copying it to the clipboard, click on the top left hand cell of your target selection. Right click on the mouse or use the menu path Edit - Paste Special. Ensure that the radio button is set to "values" if you are copying formulae and wish to output values.
Ensure that the checkbox "transpose" is ticked if you wish to transpose the rows and columns when copying the data. If your selection is 5 rows by 2 columns - transposing and copying will result in 2 rows by 5 columns.
See the relevant worksheet in the Excel sample file
Inserting Objects
Inserting objects allows you to insert objects into Excel. Use the menu path Insert - Object to choose from a number of different objects. You can either use the "create new" or the "create new from file" tab. The difference is that the second option allows you to make changes which upon being saved, will be reflected in the original source file. The first options allows you to make changes, but these will only be saved in the Excel view and not in the original file.
Cell Formats
Cell formats are usually set to General. If you experience problems with certain functions, you can set the contents of both sets of data to Text. When using functions, ensure that both sets of data have the same format.
You can use the "custom" format to derive your own specific format.
Fill Command
The "fill" command can be found using the menu path: Edit - Fill. It is useful for populating data. Highlight the cells in which you wish to have the data populated. Ensure that you have data in the first cell of the selection. Excel will use this value as a basis for the series that it produces. Choose the "Down" command off the menu if you wish to have all the values in the selection filled with the value that exists in the first cell. If you wish to increment the values by a certain amount, choose the menu option "Series". Ensure that the radio button is set to row or column - depending on your particular selection screen. Choose the step value that you wish the data to increment each time. You can set a stop value up to which the cells will be populated.
Macros
Macros are useful for automating a task which requires a lot of manually repetitive work. Use the menu path: Tools - Macro - Record New Macro. Choose a pertinent name for the macro and record it. You can assign a keystroke shortcut to the macro.
You can link an icon to the macro. Turn the view "Forms" on. Create a button. Once having created the button, you will be prompted to assign a macro to it. Choose the relevant macro. If you right-click on the "button", you will see an option which allows you to change the text on the button.
See the relevant worksheet in the Excel sample file
Add-Ins
Add-Ins are sub modules of Excel which need to be "switched on" before they can be used. You can switch them on and off using the menu path: Tools - Add Ins. Tick the checkboxes to turn each component "on". Once turned on and having clicked on the "OK" icon, you will see Excel loading the modules on the bottom left hand side of the screen.
Sorting Data
Choose the data you wish to sort and then use the menu path: Data - Sort. If you have headings in your selection set, then make sure that the radio button "Header Row" is selected. If you are just selecting the data, without the headers, then ensure that the radio button "no header row" is selected. You can choose a sort order and whether you wish to have the data sorted in an ascending or descending manner.
Filtering Data
When it comes to filtering data, you can choose to use the "auto filter" or the advanced filter options. The auto filter should be sufficient for most cases. Selecting the auto filter option will allow Excel to filter the data it finds. You can also manually select a portion of data and then click on the auto filter icon. You can select a filter by clicking on the drop-down arrow in the relevant column. You will see that the list contains all the possible entries. Choosing any one of them will then display only the rows containing that entry in the specific column. Notice that the colour of the arrow changes to blue when AutoFilter is enabled. If your data is right-justified, it might be preferable to change it to be centred or left-justified, as the filter arrows occupy the right-hand-side of the top row of cells.
The advanced filter is best described by using an example, which can be seen in the sample file.
Choose the advanced filter. Ensure that you have your criteria in separate cells - known as your "criteria range". The data you wish to filter is known as the "list range". Click on the OK icon once you have made both selections. The selection indicated below will find all the rows which have both "1" in column A and the letter "a" in column B.
If you wish to show only the first row for each duplicate entry, you must tick the field "unique records only" checkbox. You can then copy and paste the unique entries to a new spreadsheet if required.
Subtotals
Subtotals carry out the same function in Excel as the name suggests. You can choose any set of data. The sample file gives a sample example of what can be done. You might want to create subtotals at each change of colour. You can minimise the data by clicking on the "-" signs on the left hand side.
Data Validation
Data validation is quite useful for a number of reasons. It is ideal for offering a drop-down of available entries.
Ensure that you have selected the cell in which you want the drop-down to appear. Use the menu path: Data - Validation. Choose the option "list" from the "allow" drop-down. Choose your "source" which in the example below will be the cells A10:A12. Click on the "OK" icon to complete the process. You will now see the arrow appear on the right hand side of the cell. You can now copy and paste this cell to other cells which require the list.
It is a good idea to place the "source" cells far from the current view. This could make things less confusing for users who need to fill in the options.
Data validation is also useful for placing restrictions on the data that can be input into certain cells. Look at the Excel sample to get a better idea.
Pivot Tables
Pivot Tables can best be described by looking at the example file set up in the Excel spreadsheet. Pivot tables can be created using the wizard found using the menu path: Data - Pivot Table Report. There are 4 steps to the wizard. You first stipulate what form the data is. Then you stipulate where it is and how it is to be reported on. You need to choose which headers need to be allocated to rows or columns, which are to be counted and the entries that are to be assigned to the data area. You can drag and drop the fields. The last step in the wizard allows you to choose where to output the data - on the current worksheet or a new one. The last step also has an "options" tab which allows you to customise the table quite considerably.
You can turn the Pivot Table shortcut menu - which makes things a bit easier.
Vlookup
Vlookup is used whenever you wish to know if a value is found in a particular selection. Choose your result or destination cell. Functions are always written in the following format: =vlookup(....
Using the simple example found in the Excel sample. If you wanted to know whether the following 2 wage types in column A were listed in column B, you could use "vlookup" in the following manner. Place your cursor in cell C1 and type in the formula listed. The formula has 4 parts within the brackets which are separated by a comma. You need to stipulate the first cell to be tested (A1), then the range to be looked in (B1:B10), then a 1 followed by either a true or false condition. The value 1 means that the contents of the first column will be returned. False implies an exact match, whilst using True will return the closest value.
Note the $ symbols between the column letter and the row. This has been inserted to stop Excel incrementing the values by 1 each time, when copying the first cell of column C into the second, third, fourth entries. This explanation is more easily conceptualised when looking at the Excel sample file.
Concatenate
Concatenate is a useful function for joining 2 or more columns of data. As indicated in the Excel sample file, "text 1" is the first column of data, "text 2" is the second column of data etc. Having chosen the second column of data in "text 2", you'll notice that "text 3" appears. The wizard will allow you to choose 5 sets of data. Having completed the process, it is possible to edit the data and manually insert further sets of data.
Sum, Average, Count, Max, Min
The above 2 functions are rather useful and are often used. If you ensure that the cell you wish the sum to show in, is selected, you can click on the summation icon and Excel carries out a best guess of the cells it thinks you wish to sum (usually the cells immediately adjacent). You can change the target selection.
If you wish to use the "average" function you can use the Fx icon. You will then have an opportunity to use any of the available functions. Choose from the most recently used ones: mathematical, date types etc.
A shortcut offers you the opportunity of using the following functions:
- Average
- Count
- Count Nums
- Max
- Min
- Sum
If you click the right hand mouse button whilst hovering at the very bottom of the screen, you will notice that one of the above 6 functions will have a tick alongside it. You can tick any one of the 6 to change the default. If you select any number of cells with data, you will notice that the defaulted function offers a result.
The "count" function gives the number of records in the selection criteria.
The "max" and "min" give the maximum and minimum values in the selection criteria.
The "count nums" is the same as "count" when the data contains only numbers. If the data contain any non-numeric data, "count nums" will ignore this while the normal "count" function will include these values as well.
Date Functions
You can determine days of the week from different dates by using the Excel function "weekday". As can be seen in the sample Excel file, the result of the query is output in column C. The function looks at column A and decides whether it is the first, second, third etc day of the week.
The number corresponds to the second value in the formula - 1 in this case. The "1" implies that the first day of the week is Sunday. Days falling on Sunday will return 1, Monday 2, etc
Having a 2 in the formula means that the following sequence is used: Monday returns 1, Tuesday 2 etc.
Having a 3 in the formula means that the following sequence is used: Tuesday returns a 1, Wednesday 2, etc.
Split Windows
The split windows command allows you to view different parts of the same spreadsheet. Use the menu path: Window - Split to give you a vertical and horizontal split. To remove the split windows you can use the menu path: Window - Remove Split. You can also create an individual horizontal or vertical split by double-clicking on the bar immediately adjacent to the horizontal and vertical scroll bars. The cursor changes to a symbol indicating 2 horizontal or vertical arrows, separated by 2 parallel lines.
Freeze Panes
Using the "freeze panes" functionality is quite useful when you wish to see the headings - which are usually in the first row or column. The functionality is found using the menu path: Window - Freeze Panes. Click on a specific cell. The window is frozen immediately above and to the left of that cell. This can be seen in the sample Excel file. The cursor is placed in the cell B2. Scrolling to the right and down will still allow you to view the headings in the first row and column.
Consolidation
The above function is quite useful. It allows you to consolidate data from different Excel data sources. The most useful example it to consolidate data which is found on different worksheets. The task is made easier if the data is in the same format and structure. The example found on the sample Excel file accompanying this document, shows how the feature can be used in its simplest form.
Note: Formulae are only copied through as values and not as formulae.
Page Setup
You can make changes to the page setup by using the menu path: File - Page Setup. There are 4 tabs: Page, Margins, Header/Footer and Sheet.
The Page tab allows you to alternate the page layout from portrait to landscape and vice versa. You can also change the size of the print, by adjusting the percentage of the normal size - where the normal size is defaulted to 100%. Alternatively you can force the printout to be a certain number of pages wide or tall. This is useful especially once having set a print area.
The Margins tab allows you to change the 4 margins on each side as well as the height of the header and footer. You can also centre the data either vertically or horizontally.
The Header/Footer tab allows you to insert certain text items and attributes into the header and footer. For both the header and footer there is a drop-down arrow on the right hand side which offers you certain choices. If these are not sufficient, you can click on the custom header or custom footer icon to determine your own. You can determine the font, page number, total page numbers, date, time and file name.
The Sheet tab allows you to determine certain things. You can set the print area, decide what fields are to be printed at the top of each page and on the left hand side of each page, whether to print gridlines, row and column headings and other pieces of information.
Print Areas
Print areas are useful if you wish to print only a certain part of the document. Highlight the area you wish to set as the print area and use the menu path: File - Print Area - Set Print Area. You will then only be able to print out that piece of chosen data. You can clear the print area by clicking on the menu item Clear Print Area, which can be found immediately below the "set print area" command.
General Hints & Tips
- If you are unsure as to what data is required for a function. Choose the destination cell for the function and then enter "=function" in the cell - where function is sum, vlookup etc. Click on the "=" icon to the left of the cell input area. This will bring up a dialogue box, which guides you through the required procedure.
- If you need to select a column which has thousands of entries, you do not have to click the top cell and drag right down to the bottom. Highlight the top cell and holding down the shift key, double-click the bottom line of the highlighted cell. This will take you the next cell preceding an empty cell - which should be the last cell. Note: If there are any blank cells within the column, the whole column will not be selected.
- If you have created a formula in a cell. Instead of clicking and dragging the bottom right hand part of the cell to the last possible cell in the range - double click on the bottom right hand side of the cell (hovering over this you will note that the appearance of a "+" sign). This will have the same effect.
- If you wish to increment a cell by the next available value e.g. you wish to number the first 100 rows from 1-100. Place the value 1 in cell A1. Click on the bottom right hand side of the first cell and drag it down to cell A100. You will note that the values increment by 1 in each case. You can do this with dates and other types of values. Note that with certain cell formats you may need to hold down the Ctrl key to enable this.
- If you have a spreadsheet with duplicate entries in any column and you wish to only have the first record showing - you can do so using the following workaround. In a spare column write a formula which finds the difference between the cell and the one following it. Copy the formula down to the end of the rows holding the data. You will see that the result is 0 unless there is a change in the sequence. Copy the whole column (containing the formula) and use the paste special - as values, in the adjacent column. You can then sort all the data on this column to get the unique rows.
- Switch on the "Show Tip of the Day" in the options in the Office Assistant to get invaluable tips on Excel.
- When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.
- To change the author name for the active file, do so under the menu path: File - Properties.
- To make a macro available whenever you run Excel, store the macro in the Personal Macro Workbook. Remember to unhide it first, because the Personal Macro Workbook is a hidden workbook that is always open.
- To constrain an object so it moves only horizontally or vertically, press SHIFT as you drag the object.
- To save changes to a read-only file, use the Save As command (File menu) to save it with a new name.
- If you want a macro to select a specific cell, perform an action, and then select another cell relative to the active cell, you can mix the use of relative and absolute references when you record the macro. To record a macro by using relative references, make sure that Relative Reference is pressed in. To record with absolute references, make sure Relative Reference is not pressed in.
No comments:
Post a Comment