Most probably, when it is your first time to work with Microsoft Excel, you will concentrate on just completing your task instead of learning how to do the task more effectively for a faster completion of your job. However, once you’re prepared to learn regarding the keyboard shortcuts that are used in Microsoft Excel, this article will provide you the most significant and beneficial Excel keyboard shortcuts that are presently available.
Using the Excel keyboard shortcuts offers you these two benefits:
Keeping your hands on the keyboard is more convenient. It is faster to move your hand from keyboard into the mouse to the keyboard.
Your muscles will not be stressed out too much with less movement of your hands.
This article has categorized the Excel shortcuts into connected areas. Through this way, it will be easier for you to learn a collection of keyboard shortcuts one by one, rather than learning all shortcuts at once.
Note: You have to remember that those keyboard shortcuts with a plus sign ( e.g. Ctrl + O ), means that you need to hold the Ctrl key, then press the “O”, while not releasing the Ctrl key.
Excel Keyboard Shortcuts: Files
This small collection of keyboard shortcuts is probably the most important and basic among the Excel shortcuts. These shortcuts can be used to open, close and save the Excel workbooks.
- Ctrl + N: This shortcut will allow you create a new Excel workbook.
- Ctrl + S: Its function is to save the Excel workbook.
- Ctrl + O: It functions by opening an Excel workbook that exists.
Excel Keyboard Shortcuts: Editing
The following are the most common Excel keyboard shortcuts that you can use in editing the Excel workbook. The most preferred shortcut within this group is probably the Ctrl + Z.
- Ctrl + Z: This shortcut will allow you to undo the last change that you have made in your workbook. It can be used repeatedly to remove the changes many times.
- Ctrl + Y: Its function is to redo the last undo. However, it can only be used if you’ve just utilized the Undo command.
- Ctrl + C: It allows you to copy a selected item of range of items in your Workbook. Then after copying, you can paste it by using the paste keyboard shortcut.
- Ctrl + V: Its function is paste the item or range of items that you have selected in your workbook.
- Ctrl + X: It cuts your selected item or range of items in your workbook then you can place it in any region of your workbook by using the paste keyboard shortcut. Cut and copy are different from each other. Cut functions by deleting your selected item or range of items, while copy does not.
- Delete: It deletes your selected item or range of items. You can utilize this keyboard shortcut when you want to delete more than one cell at once.
- Backspace: This can be used if you want to edit an item in a cell. However, it can only be used in deleting an item, not a range of items.
Excel Keyboard Shortcuts: Menu
These keyboard shortcuts enable you to activate the different menus on menu bar even without using the mouse. But you have to note that the combination key of these keyboard shortcuts is the Alt key.
- Alt + H: Provides access to Help menu.
- Alt + W: Provides access to Window menu.
- Alt + D: Provides access to Data menu.
- Alt + T: Provides access to Tools menu.
- Alt + I: Provides access to Insert menu.
- Alt + V: Provides access to View menu.
- Alt + E: Provides access to Edit menu.
- Alt + F: Provides access to File menu.
After utilizing these keyboard shortcuts, you could utilize arrow keys to select from the menu options and press “Enter” to choose an option.
Excel Keyboard Shortcuts: Selecting
This collection of shortcuts is very helpful for a quick selection of items within the Excel workbook in different ways.
Ctrl + Spacebar: This shortcut allows you to select the existing column.
Shift + Spacebar: Its function is to select the existing row.
Ctrl + A: It is known as the “Select All” keyboard shortcut. It allows you to select all the items in your worksheet.
These are just some of the keyboard shortcuts available in Microsoft Excel. You can go for further reading to learn more about them.
Microsoft Excel is one of the most beneficial computer programs that are widely used throughout the whole world. This software is employed for making spreadsheets. Maybe, some of you may think that Excel is as simple as its definition, but it’s not actually as simple as that. This computer program has the capability to enhance efficiency, not time-consuming and the best thing is that, it has the ability to perform complex computer operations. Microsoft Excel is designed for a well organized data. In addition, MS Excel contains numerous functions and one of which allows the user to format the worksheet to find and remove errors that can cause massive problems on the spreadsheet. This specific function of MS Excel is most beneficial to those who are working with databases containing large sets of data. It helps the user for a quicker scanning of errors so that they can be easily edited or removed from the database. Microsoft Excel can be a very useful program that is utilized to accomplish complicated tasks, whether you use it at work or even at home.
Spreadsheet software like Microsoft Excel is usually utilized as databases for mailing lists, sales records, and parts inventories. Normally, databases are arranged into rows of values called records. In a record, the values in every cell or field in the row is connected like phone number, address and company’s name.
However, you would find it difficult when you are already working with huge databases. One of the common consequences that take place in a large database is that of repetition or duplication of records. This duplication can take place if:
- Several records contain one or more fields like the address or name field, which contain similar data.
- The whole record is entered into the database several times which can result in two or more similar value.
Duplication of records can cause big problems in your database so it is a very good idea to locate and eliminate those duplicate data regularly.
When you are using Microsoft Excel 2007, it is not difficult to locate duplicate values like data records, dates, numbers and formulas using conditional formatting. This article will provide you an example and the steps of using conditional formatting to locate duplicate data in Microsoft Excel 2007. But before using conditional formatting to locate duplicate values in your spreadsheet, here is a brief overview of conditional formatting.
Overview of Conditional Formatting
Conditional formatting instructs Microsoft Excel to format all the cells that meet a specific condition in a certain way. For example, you can employ conditional formatting to alter the background color of cells having duplicate data. Through this, it will be easy for you to locate duplicate values in your spreadsheet. You can also use it to alter the font color of all cells having negative values.
Of course, there is a manual method to alter the formats of cells to meet a certain condition. However, if you do it manually, it would be time-consuming since formatting cells is a repetitive procedure. Especially if you are working with a huge set of data or your data change often. That’s the reason why there is conditional formatting, which will let you to formal cells automatically to meet a certain condition.
Locate Duplicate Data by Using Conditional Formatting
- As an example, you can input the following values into cells B1 to B6: 10, 11, 15, 11, 14, 11
- The drag cells B1 to B6 on the worksheet.
- Click Home on the menu bar.
- Choose and click over the Conditional Formatting symbol that is on the ribbon and then a drop down menu will open.
- Then choose and click over the Highlight Cell Rules, then Duplicates Values. After that, the dialog box related to conditional formatting will open.
- From the list in the dialog box, select “Light Red Fill with Dark Red Text”.
- Click OK to activate you selections. Then close it.
- On your spreadsheet, you will see that cells B2, B4 and B6 are formatted having a light red background color and the text are colored with dark red since the three cells enclose duplicate data, which is the number 11.
The same procedure can be utilized appropriately to make use of conditional formatting to trace duplicate records in an Excel database, as well as duplicate dates, formulas or even other values contained in your available data range.
Microsoft Excel is a database program used practically by a lot of people at home and office. Most of the businesses and companies are using the program to tabulate data and information. This program has so many features that anyone who utilizes it should understand every bit of information and command, to use it effectively. One of the most useful features in Microsoft Excel is the Paste Special. It gives users the capability to do array of known function. Through this feature, user can create related formulas and perform mathematical functions to transpose data.
There are different ways on how to use Paste Special as some users utilize different kinds of Excel program. So, to help out all Microsoft Excel users, below are the procedures in using Paste Special in Microsoft Excel 2003 and Microsoft Excel 2007 as this is the most common program of Microsoft Excel that is being used nowadays.
Before discussing the procedures in using the feature, it is vital to know first what this program is about. Well, the Paste Special command is just like Paste command. It can select arrange cell content, cell formats and calculated numbers. This command can copy contents and format the original data, which is accessible once the desired selection is finally cut or copied. Learning the basics of what is it, is as well understanding how it could be used in Microsoft Excel program.
To help Microsoft Excel users, listed below are the ways on how to use the feature. The procedures given are use in different Microsoft Excel Program. So, choose for the one that contains the same program to what you are using.
How to Use Paste Special in Microsoft Excel 2003
- Open the computer and the files containing the data to copy. Open another Microsoft Excel spreadsheet. You can access the program through the Start Button.
- Once the entire needed database is already open, then from the spreadsheet containing the data to copy, highlight the cells that need to be copied. On the new spreadsheet, right click the cell where to paste the copied data.
- From the menu, select the “Paste Special”. Then choose your own command from the displaying menu.
- Hit the “OK” button. After hitting the “OK”, it will paste the exact command given.
Using the Microsoft Excel 2003 for Paste Special is very easy. However, there are still things to keep in mind using Paste Special in Microsoft Excel 2003. Firstly, if the paste information is not correct, immediately, choose “Undo” located at the “Edit” menu. It is vital to remember that the clipboard will stack things, so it is necessary to clear the clipboard from time to time to avoid pasting the wrong data.
How to Use Paste Special in Microsoft Excel 2007
- Choose the data you want to copy and then select “Copy”. There are two procedures on how to copy the data. First, right click the mouse within the area of spreadsheet and choose “Copy” or through the copy icon from the Ribbon Home Tab. If you are not familiar with Ribbon, it is the numbers of tools and icons located above the working area.
- On the Ribbon Home Tab click the drop down menu on “Paste” and click the “Paste Special”.
- Click the cell or array of cells where the data to be displayed and click again the drop down menu of “Paste” and choose the “Paste Special”.
- From the “Paste Special” dialog box, opt for the operation you want to do and then click “OK”. In this given command, the operation is good after you have done it correctly. There are so many choices available. For instance, you are able to transpose the list of information that you have to display in a reverse manner. You may be able to paste the value copied from another workbook or spreadsheet and then link it so that every time you edit the original cell it automatically update.
You can follow these procedures for Paste Special feature in Microsoft Excel 2007. The same with the Microsoft Excel 2003, once you pasted an incorrect data, click the Undo button instantly. In addition, as much as possible read first all the commands that you need to click to ensure of giving the right instructions to Excel.
Paste Special feature in Microsoft Excel will definitely let you have an easy Excel use when it comes to adding notes and list to spreadsheet. So, better keep in mind all the procedures taken in this article to make you capable of the right procedure in using Microsoft excel.
Microsoft Excel is the most helpful program to obtain an organized and well manageable data. It is complicated to use programs like Microsoft Excel if you do not know its features and functions. For beginners who want to use this software program, make sure to learn first the Excel features and functions. Autofilter is one of the most significant features of the Microsoft Excel in all versions. The feature allows the hiding of data numbers within the Excel’s spreadsheet.
The other valuable feature of Microsoft Excel is the Sorting. Sorting allows and enables the user to control the data within the spreadsheet in accordance with the set requirements. Both features can efficiently perform in Microsoft Excel Software program. This is called the sorting of data using the Auto filter function.
For people who deals in business and engaged with multiple data, then Microsoft Excel Autofilter sorting will help them easily to sort multiple numbers of their data.
Autofilter is defined as the tool that permits the users to arrange and manage data in numerous ways. The user finds data in easy manner and sorts the data information in a friendly way. The user gets the total counts of the definite criteria they desire once the list was filtered by choosing an item. AutoFilter is one of the functions in Microsoft Excel that enables user to make something on the data without examining it individually.
These are the procedures in sorting of data using the feature of Autofilter in Microsoft Excel.
- Choose the entire top row through clicking the number 1 or any cells you want to apply the filter.
- Click “Data”, click “Filter”, and then choose “AutoFilter”.
- After clicking “AutoFilter”, it is important to observe that the entire column heading must obtain small arrow on the right side. Then click the arrow and instantaneously so many choices are displayed. Select the data you want to appear and automatically, the selected data will come out.
- It is very important to note that by clicking the arrow you have the option to choose for the value you want. Furthermore, you also have the capability to Sort, Custom, list All and Top 10.
These are the basic procedures on how to sort data using the AutoFilter function. It is very important to keep in mind all these instructions to utilize the tool effectively in sorting data. To understand more AutoFilter Function in sorting here are other ways on how to use it in Microsoft Excel:
- Study the data and then decide for the columns or rows to sort. Highlight the chosen columns or rows and opt for “Data” located at the menu. Select “Filter” and click “AutoFilter”.
- After clicking the “AutoFilter”, you will see the drop down arrows in every title box. Every arrow can be clicked to filter the desired data in concurrence with the column. Only the chosen data that are clicked are the only item that is visible in the spreadsheet.
- Once you go to “Draft” title cell and if you click the arrow and “3/25/2008” in autofilter column, you will only have the data that matches those commands.
- In doing this, you will see that arrow located in “DRAFT” turns to blue. Once it obtains this color, it indicates that the spreadsheet was filtered.
- You can do further filters, by clicking the arrow located in “WRITTEN” cell and click the “3/28/2008”. Upon doing this, you will obtain the data that meets the set criteria that was drafted 3/25 and written in 3/28. Once again, you will see that the arrows of both cells are in blue.
- There are two possible ways to return to the original data. You may alleviate the entire autofilters in just one command. Click “Data and then click “Filter” to alleviate the check character on the “Autofilter”. With this procedure, you will bring back your entire data and lessen those boxes that are being checked. If you like to restore the data individually just click the arrow and click “all”. Then the entire data are again marked.
Through these procedures, you will easily learn and understand on how to sort data using the Auto filter in Excel. Therefore, analyzing Excel’s functions and formulas, you can easily use the sorting of data information through Auto filter within the Excel’s spreadsheet.
Microsoft Excel is one of the programs mostly used as it helps to organized data and information. It’s flexible and handy to those who want to learn about its functions and keep you well thought-out. For the initiating user of this function, you have to research first its use on how, where and why you should utilize this on your spreadsheet. However, the user must learn and understand the meaning of this function before this article discusses the main course.
Auto Filter Defined
AutoFilter is the filtering of data that are to be ignored. Auto filter can properly manage the worksheet. With the Auto filter feature, it is significant to know and learn the retrieval of hidden data. Once the user enable to know the different command function of the programs, it is now easy for the user to use this software program. The mostly used among the Excel functions is the Auto filter in spite of the fact that Excel software program has many available functions and commands. It also aims to hide certain data that are now waste and unusable.
Data Filtering with Auto Filter
Data filtering is the way to hide certain data or records focusing to other data. This is done when the user is working with multiple data. Microsoft Excel offers two approaches to filter records or data – Advanced filter and the Auto Filter. Both are required to arrange list data from different variables in various columns, different records in various rows and first row variable names of every column. This requires the user to arrange information in its list type. The main objective of Auto Filter feature is to let user to easily concentrate on the separation of records within their list.
Microsoft Excel Auto filter will ask you to treat it as a name for the first rows thus it is called Column names. Therefore, alphabet letters written on the top of the spreadsheet columns called as variable names.
Two Methods of Autofilter
AuotFilter done in two methods the first one is through dropdowns and the other one is through other criteria. The dropdown method is the easy one. This method will only hide the rows that do not meet the criteria for filtering that was set through the dropdowns. The other one is through using the other criteria. This is identified as the Custom AutoFilter. This usually supports the wildcards. Some of the symbols are used to represent certain characters.
For instance, asterisks are used to represent series of characters and question mark corresponds to a single character. This is best to use during difficult times, wherein data are too intricate to handle.
Since, Auto filter is designed to temporarily hide data, there are situation where in the data hidden are useful to complete certain information. Therefore, in order to the base a chart off Auto filtered records, it is very important to remove the Auto filter. It could perform once or individually, depending on the needed record.
There are two ways to found data that Autofilter are being Filtered. With the two methods, it is better to know first the fast method. In this method, the AutoFilter Dropdowns are the most important key for the easy charting off.
The AutoFilter is responsible for hiding certain things that are no longer useful. Therefore, to have fast method charting off data from the Auto Filtered record is through removing the entire auto filters. To remove them, here are the possible ways:
- First, click the desired data.
- Next is hit “Filter”.
- After hitting “Filter”, click the check figure in the “Auto filter”.
This is the fastest method in charting off Auto filtered records. With this, the needed data can be obtained. The command Find could be used in knowing the exact data needed. The other method is the individually or one column at a time method. Below are the procedures:
- Click the arrow in the column.
- Click “all”.
Automatically after clicking, the entire data on the column that are AutoFiltered will automatically noticeable again. This two-way procedure will take you away from the individual clicking of the column and then it helps you organize your time not to be wasted.
These two-way procedures are done mostly by Excel users to view back the original data that are Auto Filtered. Microsoft Excel Autofilter is easy to use and once you are familiar with this feature, all your multiple task using Excel spreadsheet will automatically be done in no time. Thus, this will result to the proper management of your data. Moreover, you don’t need multiple spreadsheets just to monitor all your previous and future data.
In Microsoft Excel, IF function is the most efficient and versatile function. This is also available in other worksheet or spreadsheet programs. This is one of the most used and helpful functions. It aims to test and see if a condition is false or true. If it is true, this function aims to do a single thing. If it is in false condition, it will do something more. Comparison operators are often used to the logic test for its comparison between two values.
This IF function performs different calculations that depend on what function is used, returning its true value or not. This is the second option upon using the IF function of Excel having to carry out the various calculations depending on the tested condition of false or true.
In Microsoft Excel, IsError function is used in checking for the error values. The syntax for this function is “IsError(value)”. The value argument is the important because this is the value that the user wanted to test. IsError function can be applied to Microsoft Excel XP, Excel 2003, Excel 2000 and Excel 2007.
IsError function is oftentimes used when there are errors that occur in your spreadsheet containing numerous formulas that is not calculated properly. This function, combined with the IF function are used in defaulting the value of the cell when errors occurred. Through this, your formula can evaluate properly even without the involvement of the user.
Names of First and Last Extraction:
The extraction or the splitting of the first names and last name components is the most frequent task that is encountered by many people when working with the data lists in Excel. For instance, the user have to put the full names such as “Pearson, Michael D” in column A thus it needs to put the last name in column B, the first name is in column D and in column E is the middle initial.
All the procedures and functions assuming your data is in the format of “Last name, first and middle name”.
These are the following number of worksheet functions in splitting full names into the last name and first name components.
Returning the full name’s last name in column A2, you can use this formula, “LEN(A2), Find(“,”,A2,1) -1),“left(A2, IF(IsError(find(“,”,A2,1))”
Returning the full name to first in column A2, this formula is applicable “=TRIM(IF(Is Error(FIND(“,”,A2,1).
Returning the full name to middle name, try this formula, “=TRIM(right(A2,LEN(A2) – IF(IsError(FIND(“ “, A2,FIND(“ “,A2,1) + 2))).
VBA functions are used to split full names into the first name and last name components. This instruction accepts the full name and returns the four elements of an array – the last name or surname, the first name, middle initial, and suffix. Therefore, through this function, you need to enter these names through an array formula.
In complex spreadsheet of Microsoft Excel, the user wanted to hide any errors obtained in the functions. It doesn’t only aims to make your spreadsheet looks nice but it aims to hide errors such as #REF, #VALUE and #N/A. Through this, your spreadsheet will look more professional. However, numbers of errors will blow up on other function; therefore, it will produce and give more errors. Thus, your spreadsheet or worksheet will become not viable once there is some dependent function in your spreadsheet.
Nevertheless, never use IF IsError in suppressing the errors when there are really no errors, thus you have to use these functions only when there is an error that has occurred. If the function is based on the VLOOKUP value of existence but there is no guaranteed existence of VLOOKUP function value, therefore, it is not necessary to use the functions of IF IsError. When your spreadsheet is in aVLOOKUP value, it is not suitable to suppress and trace the error. The IF(IsError) functions covers generally the potential errors such as #NULL, #VALUE, #REF, #NUM!, #NAME?, #N/A or #DIV/O!.
IfError is introduced along with the Microsoft Excel 2007 and in Microsoft Excel 2003, IsError function is used. Microsoft Excel 207 IfError is an extreme user-friendly and this function is the performance improvement over the Microsoft Excel 2003 IsError. Even though, they have several differences among them.
However, IfError is in replacement but IsError is inevitable because of the following:
- The creation of the Microsoft Excel models similar to Excel2007 and Excel 2003.
- The requirement of “IF(ISERROR(arg1),arg2,arg3)” as a substitute for “IF)ISERROR(arg1),arg2,arg3)”.
Most people don’t have the knowledge on what are custom views. What are Excel’s custom views? Custom Views in Microsoft Excel is a competent and well-organized way to view your presentation. All the names that are indicated in your custom views show all the items at a given moment. The custom views update all your files whenever you open it and they enable you to find the items in just a moment. They are being restricted in just single items such as notes or tasks. Thus, you can search automatically and combine number of mixed lists of items to the mail messages coming from the calendar. In consequence, if many messages are received meeting the criteria while the custom view is open, these messages are added automatically to the view. Through this, you save a lot of frustrations through clicking the mouse repeatedly.
How Would Custom Views Help the User on Frequent Basis?
By using custom views, you can easily search your queries. Thus, you can also connect to “save as custom view” displayed in your toolbar. In Entourage 2001, this custom view is called “messages received today” and it is located under the folders of custom views in your folder list. This is very common, through custom views you can simply view your messages in an instant instead of having the Advanced Find wherein you are to enter the criteria whenever the user wanted to see the files. The other thing that you have to know is that, contents are changing daily without the any interventions of the users, therefore, the views remains applicable and significant.
Where Can the User Find Custom Views?
If you are keen to notice that the Entourage provides a number of custom views that are pre-configured. Entourage 2004 is used and accessed through clicking the navigation button “project center”. Some of the custom views in every type of the view are folder list provided. Try to click the “expand” triangle button next in any of the view folder, and click the preferred view displaying the data that match your criteria. If you want to show the custom pre-configured views, just click the folder of custom views in the Entourage 2001 folder list.
How Does the User Create Efficient Custom Views?
It is very inconvenient when you are interrupted with your presentation flow by having to scroll and position every area when it is needed. Most people wanted to demonstrate what they want to utter through pointing out the preferred areas of your worksheets. Through Microsoft Excel, you can create your presentation using custom views. Custom views can be accessed in the toolbar list by just clicking. Custom views have the resemblance to the Advanced Find with similar numerous powerful features.
The first thing that you have to do in creating custom views is to follow these instructions below:
- Type in Go to then opt for the view for your worksheet area.
- Go to custom views
- The user has to click the Add Button then enter your view name then click OK.
These above instructions are to be repeated in case you have to create another custom views. These are instructions to create the presentation custom bar.
- Customize or connect to Go to tools.
- Connect to tool bar tab then click the New button.
- The user should have enter a name for his tool bar
- Click to the tab for commands.
- Click “View under Categories”
- Click “Custom Views under Commands” then drag this to your custom tool bar.
- Click the “close” button
For the quick custom view for your presentation, click the small down arrow on the Custom View button and then select the view you want to present from the presentation.
How the User Delete a Single Custom View?
When deleting one custom view, you need to go to the menu bar, and then go to custom views. Look for the view tab, “workbook views group” and “custom views” displayed in your monitor. You can view the views box then select your desired view name and then click the “delete” button.
Moreover, you can also use custom view to save the display settings like widths, row heights, columns, filter settings, window settings and cell selections. In addition, in print settings, you can save the margins, sheet settings, page settings, footers and headers for your worksheet. Thus, you can apply it immediately when you need another worksheet.
In Microsoft Excel, Pivot Tables are considered to be interactive tables allowing the user to summarize and group numbers of data in a tabular concise format for simple and more convenient analysis and reporting. Pivot tables aim is to count, total and sort the data, thus, this table is also available in assorted spreadsheet programs. It was called Pivot Tables because the user can drop and drag fields while rotating the summary fields thus creating cross tabs. One of the advantages of the Pivot Table is to provide an alternating data view. This is generally used in many cases of repetitive data that are difficult to analyze and read.
Steps to Create Pivot Table:
- Start by launching Microsoft Excel
- Download the data “PivotTable Source File”
- Click Data menu then select PivotTable and then click Pivotchart Report
- Answer the question
- Specify the data location you have to summarize
- Click the “Next” button.
- Ensuring the option for New Worksheet is chosen, once the user clicks the “Next” button from the previous step, and he or she can click the “Finish” button.
- Assemble or gather the Pivot Table
- Look for the Data. For instance, you want to see your sales representative’s accomplishment, all you have to do is to add the “SalesRep” field to the spreadsheet row area. Then click on the button that shows SalesRep, then change the decline to its row area and then click the button Add.
- Then, add its total field to the area of data.
- However, if you want to view their sales in every category, how would you do it? Well, just add the “Category Field” to the area of the column.
- If you don’t want condiment category to your analysis then you have to click the down arrow just beside deselect condiments and category.
- Click on the OK button. In this way, you will notice the recalculated grand totals not to include the condiment category results.
- To make your Pivot Table look more better and great, there available massive amount of Autoformats
- You are now done with your new Pivot Table.
Microsoft Excel is the most powerful and fantastic tool in analyzing data and report trends, findings and various data relationships. Sometimes, the user must produce various reports based on the data presented, to analyze numbers of data and then present it to your friends and colleagues. People must be thankful that there is Excel that has shortcuts in it. These Excel shortcuts are called reports and Pivot tables. Several of the beginners who are using Excel are intimated by reports and pivot tables since it was considered hard. Some learned it in school or some people gained knowledge about its function but not able to remember it extensively since it’s not used everyday.
Be reminded that pivot table can evaluate or examine numeric relationship in just a click. Thus, produce charts and reports with just a click even without going to different steps in creating report or chart.
- Open the Microsoft Excel Application then go to “File>New” to open the new spreadsheet. Then enter the data into spreadsheet and you need to format it accordingly.
- Move to Pivotchart Report and choose option
- Click on the first option database. Below its first question, click on the first option “Pivot Table”. Below the secondary option, click the Next button.
- Choose the data area, click enter and hit the Next button.
- Hit the first option, generate the table in the new spreadsheet, and click on the Finish button.
- Now, the user is building the Pivot Table through dragging items from the Field of Pivot Table or in the list of pop-up window. The fastest way is to choose “drag and drop”. Thus, you can choose and drop any items to the “drop column Fields”, “Drop Row Fields” and “Drop Data Items”. All these combinations are endless thus; the user can see quickly how various items will relate to one another.
- Click on the “City” button and drag it to the “Drop Columns Field” section.
- Hit the salesperson then drag it to “Drop Row Fields” section.
- Hit the Accounts Opened then drag it to the section of “Drop Data Items”. Now, the following two steps are to create a chart and report through clicking on its available options.
- Move on to the format menu of the Pivot Table then choose format report, through this, you can see all the report options series.
- If you want to see the information in a chart, then you have to go to the option of Pivot Chart.
Pivot Table is used in Microsoft Excel spreadsheet to present quantitative data in an easy and user-friendly manner. To manipulate data in a Pivot Table it must have variables that exhibits value or values for data to be categorized or grouped. To create a Pivot Table you must have a data or values in Excel or other form of data from which it could be imported. Before one can manipulate the Pivot Table it must first contain the data and its name for record or labeling purposes. When the Pivot Table is already created, the next step would be displaying the data in line with the values presented in each field. To do so, you must hold down the left button of the mouse then release the mouse button on the end-column.
Before manipulating the Pivot Table you should first learn the different methods in presenting a group of different values which is to be presented in the Pivot Table. If the data to be grouped is in the Row or Column select on or more data by clicking on the data while pressing Ctrl keys. After highlighting, click on the right button of the mouse and select on Group and Outline. Another method is grouping through formulas. Do the same selection process then click on the right button and select Formulas. The last method is the Hide items method. The method starts on clicking on the Page field then select Hide items. All the shaded or selected boxes with the values you want to hide will be hidden.
To manipulate data in the Pivot Table such as in adding or removing click on the field and add the label of the values. In your Pivot Table you can also turn the table such as moving the rows and columns to see the variety of data summary. To filter data is another manipulation in the Pivot Table; this is to manage large data. To achieve this filter, select on page field to separate the table in different pages. The page will eventually contain sub-data of a summary of the main Pivot Table. To create a consolidated report for easy understanding, one can make the page fields to limit the quantity of data. This could give you an easy time on the waiting and memory matter. You can also manipulate the data by showing only the data you want to see or be visible.
The data or variables that is seen in the center of the Pivot Table can be changed or stated in sum, average, mean, count, total, and others. In order for you to change or alter the data which is being presented just click once the right button of the mouse and in Office2000 or Office97 select and click on Field or Field Setting. After, select and click on Summarize by: operation. Though the Pivot Table shows one field of data, there are still other available choices in the Pivot Table Field box such as to alter the name of the field, change number formatting, and you can even delete a variable.
Another manipulation of data in the Pivot Table is converting or displaying rows or columns in percentage. To have a total of 100% when you add a column, a row, the whole table, you just have to follow the previous data manipulation abovementioned. After which, click on Options to be able to extend the Pivot Table, and select and click Show Data As, then Normal Selection, and choose your format.
There are still other options available for field variables. Select and click-on Field variable, then click-on Options, after select Advanced. This selection gives the user the permission to show the values for the selected field and sort it on the table. The selection can be in ascending or descending order, you can also limit the values of the data with the highest possible value such as 10.
To manipulate data from a list or from a data base and transfer it to your Pivot Table you must first create a list or your data base where the source of Pivot Table. Double-click the Grand Total of the Pivot Table. Choose the cell that fits to the criteria and then double-click; this is for you to visibly see the source of your records which fits the certain variable.
These are just the main and basic manipulation of data I a Pivot Table. There is still an array of manipulative process which you can use for easy and accessible usage of the table.
A Pivot Table is very useful in deriving meaning from data. This is also very helpful in analyzing and getting an overview on the relationship of the data between the columns and the rows. Manipulation of data, viewing of data in different perspective, and other data related action can be done in the Pivot Table.
After you have created the Pivot Table and inputting data from your existing data on the workbook you can update the data in the Pivot Table. Also, while updating your data you can also change the design of the Pivot Table with your own preference without altering the current data and information n your table. Aside from giving you the freedom to update the data in the Pivot Table, you can also organize the data in a very organized manner. But, before anything else you must input first the data in Microsoft Excel.
Also, while inputting your data do not forget to set or select the “preserve formatting on update” selection or dialogue box. In this manner, any update on the data on the Pivot Table will automatically take place. Also, this command will immediately save changes on the data and even after refreshing it. Likewise, remember that the update on the data of the Pivot Table will only take place if the original data is updated or changed.
After updating the source of the data you can now update the data on the Pivot Table. The process would first start on placing the cursor in the worksheet where the original data is placed or on the database. After doing so, place or point the cursor on the cell of the data you will be updating. Now, change the numerical data. After, you can now return or o back to the Pivot Table and check the update. The steps done were the updating of the data in the database of the original table in Excel.
Next step is to update the data on the Pivot Table. First, go back to the worksheet where the Pivot Table is. After, find the image which has an exclamatory point mark which has a red color. Then click on the image to automatically generate the data which was changed in the original worksheet or database. The update or changes made on the must automatically take place after clicking the image because if you won’t click on the image no updates will take place.
In another manner, if the Pivot Table you have created is linked to another Pivot Table but still in the similar workbook, in another workbook, or unto another source or data base, you can always perform the operation to refresh to update the data on you Pivot Table. Every time you update your data base, make sure to refresh your Pivot Table to acquire the recent updates on the data you have inputted on your data base. Also, you can command that the Pivot table be automatically refreshed every time you add, input, or update data.
To manually refresh or update data on your Pivot Table just click on the Pivot Table workspace. The PivotTable Tools box will now be visible on the workspace with the adding Options and the Design tab. Select on the Options tab, click on the Data group and press Alt+F5 or hit the Refresh button. Also, you can click the right click of your mouse and click on Refresh. To be able to refresh and update all data in the Pivot Table workbook, click on the Refresh button and select on Refresh All. To maintain the same format of the Pivot Table, select first the Autofit Column Width on Update and then select Preserve cell formatting on update.
To review the status of your data or to cancel the refresh command you can do it by hitting Options tab, select on the Data group, and click on the Refresh button row. After doing so, click the Refresh Status for you to be able to see the current status or hit Cancel Refresh to stop the update.
You can also automatically refresh the inputted data when you open your Pivot Table workspace or file. Here is how, click on the report anywhere, this will eventually display the PivotTable Tools wherein you will see the adding Options with the Design tab. Now, on the PivotTable group select on Options. And, in the Pivot Table Options dialog box you will see the Data tab, on the Data tab select the Refresh data when opening the file.
- How to Use the MS Excel Keyboard Shortcuts
- How to Locate Duplicate Records Quickly By Using Conditional Formatting in MS Excel
- How to Use PasteSpecial – Procedures for Microsoft Excel 2003 and Microsoft Excel 2007
- How to Sort using an AutoFilter – Understanding and Learning the Feature
- How to Base a Chart Off of Auto Filtered Records
- How to Use IF IsError Together?
- How to Use Custom Views
- How to Create Pivt Table
- How to Manipulate PivotTable after It has Been Created
- How to Update the Data in a Pivot Table
- How to Create Dynamic Named Ranges
- How to Use an Array
- How to Embed 8 If Statements into 1
- How to Edit a Recorded Macro?
- How to Record a Macro