Cập nhật nội dung chi tiết về How To Use Excel Styles Efficiently mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Learn what styles are and how they can help you format your sheets more effectively and efficiently.
Illustration: Lisa Hornung
Styles are a Microsoft Word feature, right? You might be surprised to learn that Microsoft Excel uses styles too, although the nature of the data doesn’t require the same kind of robust options. Excel styles are easier to use than Word’s. If you’re not using them because you think they’re complicated, you might want to reconsider. In this article, I’ll introduce you to Excel styles and show you how to use them to use more efficiently in Excel.
I’m using (desktop) Office 365 but you can use an earlier version. You can work with your own data or download the demonstration .xls file. Styles are available in the browser edition.
LEARN MORE: Office 365 for business
What’s a Microsoft Excel style?
If you don’t work with styles, you might be wondering what’s so special about them. The easy answer is, they help you work more efficiently. A style is a collection of formats that you can apply simultaneously. For instance, you might want all input cells to have a green background with white font. By using a style, you can apply both formats at the same time. Now, that doesn’t sound like a big deal, but there’s more. If you decide to change the format for input cells, you don’t have to change all those cells individually. You simply change the style and all input cells update automatically-now that’s working efficiently.
Styles in Microsoft Excel
Figure A
Styles aren’t as obvious in Excel (and other Office 365 apps) as they are in Word. On the Home tab, you’ll find styles in the Styles group, Format a Table, and Cell Styles. We’re going to work with the Cell Styles option. As you can see in Figure A, the dropdown offers several built-in cell styles.
Whether or not you realize it, you’re using a style called Normal-that’s the sparse formatting you see when you enter data in a new sheet. Toward the bottom of the gallery ( Figure A) you’ll see Comma, Comma[0], Currency, and so on. You’re also already using these styles when you choose a from from the dropdown in the Number group.
Working with styles in Excel
This bit is important: If you see formatting but Normal is selected in the Styles gallery, you know that you’re seeing direct formatting; that format was applied manually. Direct, or one-off, formatting is fine, but when you start mixing it with styles too much, things can get confusing. It’s better in the long run to modify the style than to manually apply more formatting to a cell that has an applied style.
Select Modify.
Let’s see what happens to cell I9 when we modify the Input style:
Now, let’s make a more drastic change. Let’s suppose that you want to add bold to the italicized cells in columns E and G and change the font color to dark green. Now, you could use direct formatting on one of those cells and then use the Format Painter, but modifying the applied style is quicker and guarantees consistency in the future. Let’s try that.
From the Color dropdown, choose dark green (Figure E).
Figure E
First, check one of the cells to see what style is applied. (I showed you how to do that earlier. In this case, the style is Explanatory. With the gallery open, continue as follows:
Figure F
We updated the Explanatory style, changing the formatting for several cells at once. It’s important to remember that if you have other cells with the same style, they will update, too. In most cases, that’s what you’ll want. If not, you might need to create a new style. Use the Duplicate option instead of choosing Modify (in the gallery), then name the new style and make changes. It’s quicker than starting from scratch.
One more thing
Cell styles reflect the theme that’s applied to the entire workbook file. When you change one theme to another, cell styles change accordingly.
Excel cell styles are easy to implement. Knowing they exist and how to determine what’s in use and how to modify a style are the keys to using them efficiently.
Also see
How To Format Your Spreadsheets In Excel With Styles
When you want to format cells in Microsoft Excel, you can do it manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. If you used styles in other programs, you’ll be familiar with the concept: a style is a mixture of formatting that you can apply over and over, like paint.
Speed. When you have a lot of cells to format, it’s faster to apply a style than to apply all the formatting features individually. And if you need to change a formatting feature of all the cells – like just the color or the font – changing the style definition will immediately update the cell formatting.
Consistency. When formatting a lot of cells, it’s easy to make a mistake and select a slightly different color or font size. But when you apply styles, the exact, same formatting gets applied every time.
Excel has built-in styles that you can use, and you can also modify them and create your own. Here’s how.
If you want to follow along with this tutorial using your own Excel file, you can do so. Or if you prefer, download the zip file included for this tutorial, which contains a sample workbook called excel styles.xlsx.
Using Built-In Styles
Excel has several built-in styles that you can use, so let’s start with those. First, select the row of column headers in the first worksheet of the workbook (B4:F4).
Roll the mouse pointer over any of the styles to see a preview of how it looks on the worksheet (Windows, only). Note: the style names, like Good, Bad, and Title, are just suggested uses. You can use any style for any purpose you want.
Now go to the second worksheet (Sales by Customer) and repeat: select the column headers along row 5 and apply the same style. Deselect to get a better look
So far, it doesn’t seem very impressive, but here’s where it gets cool: changing the formatting by changing the style.
Modifying a Built-In Style
The dialog box that appears shows what parts of the formatting are included in the style.
So for example, you might have a style that includes fonts and color information, but doesn’t affect number formatting. Or vice-versa. For this example, we’ll change the font, color and background, and add alignment to the style.
Alignment tab: set Horizontal alignment to Center
Font tab: choose a heavier weight font and slightly larger size (I chose Franklin Gothic Medium, 12 pt.), white color
Fill tab: choose a dark color
Even better, you don’t have to depend on the built-in styles. You can create your own.
Creating and Applying a Custom Style
The best way to create a custom style is to format a cell the regular, manual way, then create a style from the example formatting. We’ll do that with the worksheet titles.
With the merged cell selected, apply the following formats:
Different boldfaced font (I chose Impact)
Larger size
Dark background color, light foreground color
Thick box border
Vertically centered
Using the Custom Style in Another Workbook
When you create styles, they’re available only in the sheets of the workbook where you created it. To use custom styles in other workbooks, you need to import them.
Open the file called Merging Styles.xlsxs, which is the other file included with this tutorial. Or you can use your own file. But don’t close the workbook you’ve been working on. It must remain open for you to get the styles.
Now we can use the styles we just imported. Select the column headers along row 5 (A5:M5), then apply the Accent 1 style. Select and merge the top 13 cells (A1:M1), make row 1 taller, then apply the Sheet Title style.
Conclusion
Now you can see why styles are so great: it’s faster to apply a style than it is to apply multiple formatting attributes, and there isn’t any chance that cells of the same style will have different attributes because of human error. Also, when you change the look of a style, all cells formatted with that style will change immediately. And to use the styles from one workbook in another workbook, remember that they both have to be open.
How To Use The Data Model In Excel
Excel can analyze data from many sources. But are you using the Data Model to make your life easier? In this post you learn how to create a pivot table using two tables by using the Data Model feature in Excel.
What is a Data Model
Excel’s Data Model allows you to load data (e.g. tables) into Excel’s memory. It is saved in memory where you don’t directly see it. You can then instruct Excel to relate data to each other using a common column. The ‘Model’ part of Data Model refers to how all the tables relate to each other.
Old school Excel Pro’s, use formulas to create a huge table containing all data to analyze. They need this big table so that Pivot Tables can source a single table. Yet by creating relationships, you surpass the need for using VLOOKUP, SUMIF, INDEX-MATCH formulas. In other words, you don’t need to get all columns within a single table. Through relationships, the Data Model can access all the information it needs. Even when it resides in multiple places or tables. After creating the Data Model, Excel has the data available in its memory. And by having it in its memory, you can access data in new ways. For example, you can start using multiple tables within the same pivot table.
A Simple Task
Imagine your boss wants to have insight in the sales but also wants to know the Sex of the sales person. You got below dataset containing one table with the sales per person and another table containing the salespeople and their respective sex. A way to analyze your data is to use a LOOKUP formula and make a big table containing all information. As a next step you can then use a Pivot Table to summarize the data per sex.
Advantages of the Data Model
Checking and updating formulas may get arbitrary when working with many tables. After all, you need to make sure all the formulas are filled down to the right cell. And after adding new columns, your LOOKUP formulas also need to be expanded. The Data Model requires only little work at setup to relate a table. It uses a common column at the setup. Yet columns that you add later, automatically add to the Data Model.
Working with big amounts of data often results in a very slow worksheet due to calculations. The Data Model however handles big amounts of data gracefully without slowing down your computer system.
Excel 2016 has a limit of 1.048.576 rows. However, the amount of rows you can add to the memory of the Data Model is almost unlimited. A 64-bit environment imposes no hard limits on file size. The workbook size is limited only by available memory and system resources.
If your data resides only in your Data Model, you have considerable file size savings.
Add Data to Data Model
You will now learn how to add tables to the Data Model. To start with, make sure your data is within a table. Using Power Query you can easily load tables into the Data Model.
Select From Table / Range
In the home tab of the Power Query editor
Select Only Create Connection
Make sure to tick the box Add this data to the Data Model
This adds the data to the Data Model. Please make sure to do these steps for both tables.
Creating Relationships Between Data
After adding your data to the Data Model, you can relate common columns to each other. To create relationships between tables:
The Power Pivot screen will appear.
Using the Data Model
Now we come to the exciting part. To use the Data Model in a PivotTable perform the following steps:
The ‘Create PivotTable’ pop-up screen will appear. As you have a Data Model in place, you can now select to use it as data source.
In the PivotTable Fields you will now see all the possible Data Sources for your PivotTable. The yellow database icon on the lower right corner of the marked tables, shows that it is part of Excel’s Data Model.
As the two tables have a relationship between each other, you can use fields from both tables within the same pivot! Read previous sentence again. Isn’t that amazing?? Below example uses the Sales and Seller field from the ProductSales table, while the Sex field comes from the other table. And the numbers are still correct!
Using the Data Model you can analyze data from several tables at once. All without using any LOOKUP, SUMIF or INDEX MATCH formula to flatten the source table. Yet the data analyzed could also come from a database, text file or cloud location. The possibilities are endless.
To minimize the usage of LOOKUP formulas even more, an amazing tool to look at is Power Query. There’s several articles to find about it on my website. For example, you can read how to use Power Query for Creating Unique Combinations or for Transforming Stacked Columns.
Categories Excel report this ad Tags Data Model, Pivot Table, Power Pivot
How To Use The Excel Vlookup Function
VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the first column of the table passed into VLOOKUP. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.
V is for vertical
The purpose of VLOOKUP is to get information from a table organized like this:
Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:
=
VLOOKUP
(
1004
,
B5:F9,
4
,
FALSE
)
// returns "Sue Martin"
For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.
VLOOKUP is based on column numbers
When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, provide the appropriate number as the “column index”. For example, the column index to retrieve the first name below is 2:
The last name and email can be retrieved with columns 3 and 4:
=
VLOOKUP
(
H3,
B4:E13,
2
,
FALSE
)
// first name
=
VLOOKUP
(
H3,
B4:E13,
3
,
FALSE
)
// last name
=
VLOOKUP
(
H3,
B4:E13,
4
,
FALSE
)
// email address
VLOOKUP only looks right
VLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values:
If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.
Exact and approximate matching
VLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is “range_lookup“. This is a confusing name, because it seems to have something to do with cell ranges like A1:A10. Actually, the word “range” in this case refers to “range of values” – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades.
It is important to understand that range_lookup defaults to TRUE, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set range_lookup to FALSE to force exact matching:
=
VLOOKUP
(
value,
table,
col_index)
// approximate match (default)
=
VLOOKUP
(
value,
table,
col_index,
TRUE
)
// approximate match
=
VLOOKUP
(
value,
table,
col_index,
FALSE
)
// exact match
Note: You can also supply zero (0) instead of FALSE for an exact match.
Exact match
In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:
The formula in H6 to find Year, based on an exact match of movie title, is:
=
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
)
// FALSE = exact match
Approximate match
In cases when you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:
=
VLOOKUP
(
C5,
$G$5:$H$10,
2
,
TRUE
)
// TRUE = approximate match
VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will “step back” and match the previous row.
Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.
First match
=
VLOOKUP
(
E5,
B5:C11,
2
,
FALSE
)
// returns 17
Wildcard match
The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table after typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify the exact match mode by providing FALSE or 0 for the last argument, range_lookup. The formula in H7 retrieves the first name, “Michael”, after typing “Aya” into cell H4:
=
VLOOKUP
(
$H$4&
"*"
,
$B$5:$E$104,
2
,
FALSE
)
Read a more detailed explanation here.
Two-way lookup
Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:
=
VLOOKUP
(
H4,
B5:E13,
MATCH
(
H5,
B4:E4,
0
),
0
)
For more details, see this example.
Note: In general, INDEX and MATCH is a more flexible way to perform two-way lookups.
Multiple criteria
The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example below, Column B is a helper column that concatenates first and last names together with this formula:
=
C5&
D5// helper column
VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:
=
VLOOKUP
(
H4&
H5,
B5:E13,
4
,
0
)
For details, see this example.
Note: INDEX and MATCH and XLOOKUP are more robust ways to handle lookups based on multiple criteria.
VLOOKUP and #N/A errors
If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just means “not found”. For example, in the screen below, the lookup value ”Toy Story 2″ does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:
One way to “trap” the NA error is to use the IFNA function like this:
The formula in H6 is:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
"Not found"
)
The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string like this:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
""
)
// no message
The #N/A error is useful because it tells you something is wrong. In practice, there are many reasons why you might see this error, including:
The lookup value does not exist in the table
The lookup value is misspelled, or contains extra space
Match mode is exact, but should be approximate
The table range is not entered correctly
You are copying VLOOKUP, and the table reference is not locked
Read more: VLOOKUP without #N/A errors
More about VLOOKUP
Other notes
Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.
Bạn đang đọc nội dung bài viết How To Use Excel Styles Efficiently trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!