For more information, see Create a simple formula. To distinguish between different types of numbers, add a format, like currency, percentages, or dates. Click the Home tab, and then click the arrow in the General box. For more information, see Available number formats. That lets you quickly filter or sort your data. Click the Quick Analysis button in the bottom-right corner of the selection. Click Tables , move your cursor to the Table button to preview your data, and then click the Table button.
Click the arrow in the table header of a column. To filter the data, clear the Select All check box, and then select the data you want to show in your table. For more information, see Create or delete an Excel table. The Quick Analysis tool available in Excel and Excel only let you total your numbers quickly. Click Totals , move your cursor across the buttons to see the calculation results for your data, and then click the button to apply the totals. Conditional formatting or sparklines can highlight your most important data or show data trends.
Explore the options on the Formatting and Sparklines tabs to see how they affect your data. For example, pick a color scale in the Formatting gallery to differentiate high, medium, and low temperatures. Learn more about how to analyze trends in data using sparklines. The Quick Analysis tool available in Excel and Excel only recommends the right chart for your data and gives you a visual presentation in just a few clicks.
Click the Charts tab, move across the recommended charts to see which one looks best for your data, and then click the one that you want. Learn about other ways to create a chart. Select a range of data, such as A1:L5 multiple rows and columns or C1:C80 a single column. The range can include titles that you created to identify columns or rows. Click to perform an ascending sort A to Z or smallest number to largest. Click to perform a descending sort Z to A or largest number to smallest.
In the Order list, select the order that you want to apply to the sort operation — alphabetically or numerically ascending or descending that is, A to Z or Z to A for text or lower to higher or higher to lower for numbers.
For more information about how to sort data, see Sort data in a range or table. Click the arrow in the column header to display a list in which you can make filter choices. To select by values, in the list, clear the Select All check box. This removes the check marks from all the check boxes. Then, select only the values you want to see, and click OK to see the results.
For more information about how to filter data, see Filter data in a range or table. Under Save As , pick where to save your workbook, and then browse to a folder. Preview the pages by clicking the Next Page and Previous Page arrows. The preview window displays the pages in black and white or in color, depending on your printer settings.
On the File tab, choose Options , and then choose the Add-Ins category. Near the bottom of the Excel Options dialog box, make sure that Excel Add-ins is selected in the Manage box, and then click Go. In the Add-Ins dialog box, select the check boxes the add-ins that you want to use, and then click OK.
If Excel displays a message that states it can't run this add-in and prompts you to install it, click Yes to install the add-ins. For more information about how to use add-ins, see Add or remove add-ins. Excel allows you to apply built-in templates, to apply your own custom templates, and to search from a variety of templates on Office.
By selecting the Gryffindor filter, the other rows disappear. Pro Tip : Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet. Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.
To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab and select "Remove Duplicates" which is under the Tools subheader in the older version of Excel. A pop-up will appear to confirm which data you want to work with.
Select "Remove Duplicates," and you're good to go. You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows with Harry Potter's information and you only need to see one, then you can select the whole dataset and then remove duplicates based on email.
Your resulting list will have only unique names without any duplicates. When you have rows of data in your spreadsheet, you might decide you actually want to transform the items in one of those rows into columns or vice versa.
It would take a lot of time to copy and paste each individual header — but what the transpose feature allows you to do is simply move your row data into columns, or the other way around.
Start by highlighting the column that you want to transpose into rows. Right-click it, and then select "Copy. Right-click on the cell, and then select "Paste Special. Check that box and select OK. Your column will now be transferred to a row or vice-versa. What if you want to split out information that's in one cell into two different cells?
For example, maybe you want to pull out someone's company name through their email address. Or perhaps you want to separate someone's full name into a first and last name for your email marketing templates. Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select "Text to Columns. In the example case below, let's select "Delimited" so we can separate the full name into first name and last name.
Then, it's time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or something else. In our example, let's choose the space. Excel will then show you a preview of what your new columns will look like. When you're happy with the preview, press "Next. When you're done, click "Finish. In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.
You can also use parentheses to ensure certain calculations are done first. Conditional formatting allows you to change a cell's color based on the information within the cell. If you want to color code commonalities between different rows in Excel, you can do that.
This will help you quickly see information that is important to you. To get started, highlight the group of cells you want to use conditional formatting on. Then, choose "Conditional Formatting" from the Home menu and select your logic from the dropdown.
You can also create your own rule if you want something different. A window will pop up that prompts you to provide more information about your formatting rule. Select "OK" when you're done, and you should see your results automatically appear. Sometimes, we don't want to count the number of times a value appears.
Instead, we want to input different information into a cell if there is a corresponding cell with that information. For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they should get ten points.
In general terms, the formula would be IF Logical Test, value of true, value of false. Let's dig into each of these variables. Note : In the example above, I awarded 10 points to everyone in Gryffindor.
If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum. Have you ever seen a dollar sign in an Excel formula?
When used in a formula, it isn't representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows. You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you're actually referring to a cell that's five columns to the left C minus A and in the same row 5.
This is called a relative formula. When you copy a relative formula from one cell to another, it'll adjust the values in the formula based on where it's moved. But sometimes, we want those values to stay the same no matter whether they're moved around or not — and we can do that by turning the formula into an absolute formula. Learn more on Microsoft Office's support page here.
Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet? For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.
Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.
In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1. In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses.
Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. Here are the main differences:. Let's look at an example.
Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. You can open an existing workbook over here or start with a template. The area down here is where you create your worksheet.
And you'll find all the tools you need to work on it, up here, in this area called the ribbon. In this area, you'll find the name box and formula bar. You'll see what those do as we go along. Now click somewhere in the work area. Let's say we want to create a worksheet to track expenses on an expansion project. Type the first budget item, and press Enter. For example, the address of this cell is C6; column C, row 6.
This is a breakdown of the work required for the expansion project. Then, click and drag the border to widen the column. First, select the numbers by clicking the first number and dragging the mouse down the list.
The gray highlighting and green border mean the cells are selected. The mini-toolbar changes depending on what you select.
In this case, it contains commands for formatting the cells. Now it is beginning to look more like a worksheet. Next, let's do something to the data to make it easier to work with. Select the header and data. Click the top left corner, and drag the mouse to the bottom right. Click it and the Quick Analysis lens opens.
This contains a set of tools for helping you analyze your data. You don't have to do this, but working with data as a table has certain advantages. For example, you can click these arrows to quickly sort or filter the data. You also have a lot of commands and options to choose from, up here on the ribbon. While we're up here, let's take a closer look at the ribbon. The commands and options you can work with are organized into these tabs.
Most of the commands, you'll need are on the HOME tab.
0コメント