Is there any way to make Microsoft Excel less scary and intimidating? By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light. We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data. To start you off, get yourself familiar with formatting your spreadsheet cells.
A visually organized spreadsheet is highly appreciated by others as it can help them follow your data and calculations easily. To quickly apply your formatting across hundreds of cells, use the Format Painter:.
To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. In fact, it's quite simple to make a pie chart or a line graph , and once you've tried your hand at those, other graphs are fairly simple as well. While pivot tables seem intimidating, they're actually easy to create and give you the ability to gain deep insights into the data in your spreadsheets. In essence, they let you "pivot" how you view the information in a spreadsheet.
For example, suppose you had a worksheet that summarized the fat and calories for a variety of foods. The tabular form of this spreadsheet makes it hard to interpret. With a few clicks, you can turn it into a pivot table with an accompanying pivot chart.
Click a blank cell in the worksheet with the data you want to visualize. Click the "Insert" tab and then click "PivotTable. Using your mouse, select a range of cells that includes the entire set of data. You should see the selection appear in the "Create PivotTable" box.
Click "OK. In the new "PivotChart Fields" pane, click two axes you want to display. For example, in this worksheet, you could choose "food" and "calories," which would show a table and graph of how many calories are in each type of food.
That's convenient but doesn't offer a lot of additional insight compared to the original data. But deselect food and instead correlate calories to the category of food — now you can see how each category compares in total calories, because the pivot table and chart sum the calories of all the items within each category and display the results. This is an insight that's not easy to see when looking at the worksheet in its original form.
Your workbook can be saved to your computer or the cloud. You can find the AutoSave control at the top left of the Excel window — swiped it to the right to autosave, or to the left to disable this feature. To save the workbook, click the "File" tab and then choose "Save As" from the File pane.
Choose a location and click "Save. You can also print a worksheet from the File tab as well. In the File pane, choose "Print" and select what you want to print in the Settings section. For example, you can print just a certain part of a spreadsheet or print everything in the workbook.
In recent years, Microsoft has eliminated its standalone Office products like Word, Excel, and PowerPoint, and Excel is available exclusively as a part of a Microsoft subscription. There are several plans available, including Microsoft Personal , Microsoft Family , as well as several versions of Microsoft Business , and Microsoft Enterprise.
Each of these generally come with the full suite of Office apps and OneDrive cloud storage, though they vary by price and which specific features are included. 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. 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.
0コメント