Cập nhật nội dung chi tiết về How To Format Your Spreadsheets In Excel With Styles 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.
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.
Cách Xóa Style, Delete Styles, Xóa Formating Style Cứng Đầu Trong Excel
Các bạn tải file Excel trên mạng sẽ rất nhiều khi gặp phải trường hợp trong Formating Style có rất nhiều Style không mong muốn. Vì vậy các bạn muốn xóa Style nhưng có nhiều Formating Style cứng đầu không thể xóa được.
Cách 1: Xóa Style thủ công
Cách này chỉ áp dụng cho số lượng Style cần xóa nhỏ, các bạn thực hiện như sau:
Xuất hiện các Formating Style, các bạn muốn xóa Style nào thì nhấn chuột phải vào Style đó và chọn Delete.
Cách 2: Xóa Style sử dụng code VBA
Với file Excel có nhiều Formating Style thì cách này sẽ giúp các bạn xóa tự động tất cả các Style trên file Excel, các bạn thực hiện như sau:
Visual Basic. Sub StyleKill() Dim styT As Style Dim intRet As Integer On Error Resume Next For Each styT In ActiveWorkbook.Styles If Not styT.BuiltIn Then End If Next styT End SubBước 5: Nhấp đúp chuột trái vào StyleKill để chạy Module xóa Formating Style.
Cách 3: Xóa Style bằng cách xóa trên file Style.xml
Xuất hiện hộp thoại Folders Options các bạn chọn thẻ View, trong phần Advanced settings các bạn bỏ chọn trong ô vuông trước dòng Hide extensions for known file types và nhấn OK.
Bước 1: Mở file Excel .xlsx (hoặc .xls) sau đó các bạn nhấn phím F12 để mở cửa sổ Save As, lưu thành bản mới với định dạng .xlsm như hình dưới.
Bước 2: Vào thư mục chứa file .xlsm mà bạn vừa lưu, sửa định dạng .xlsm thành định dạng .zip. Xuất hiện thông báo Rename các bạn chọn Yes.
Bước 3: Nhấp đúp chuột để mở file zip, tiếp theo chọn thư mục xl.
Bước 4: Mở file chúng tôi bằng Notepad.
Bước 7: Ngay khi các bạn lưu và đóng file chúng tôi thì trên thư mục winrar của file Excel mà bạn đang mở sẽ xuất hiện thông báo file chúng tôi đã sửa đổi, các bạn chọn Yes và đóng file .zip.
Bước 8: Đổi lại file từ định dạng .zip thành .xlsm
Các bạn mở file Excel, xuất hiện thông báo We found a problem with some content… các bạn chọn Yes. Như vậy các bạn hãy xem kết quả, sau đó lưu lại định dạng .xlsx hoặc .xls tùy ý.
How To Use Excel Styles Efficiently
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 Run Vba Code In Your Excel
VBA is an excellent tool in Microsoft Office suite, including MS Excel application. It can assist you to accomplish a lot of tasks not supported by the native features in Excel. Now, in this article, we will teach you to run VBA code in Excel step by step.
If you intend to write some VBA codes to achieve some of your requirements, you have to firstly figure out how to run VBA code in your Excel. Now, thereinafter, we will use MS Excel 2010 for an instance to show the detailed steps, including how to enable macro, trigger VBA editor and several means to run macro, etc.
Step 1: Show “Developer” Ribbon
First off, launch Excel application.
In the popup “Excel Options” window, turn to “Customize Ribbon” tab.
Step 2: Change Macro Security Settings
Since Excel comes with macro auto disabled in security settings, you need to first change the settings by following the steps below:
At first, in Excel main window, switch to “Developer” ribbon.
Next, in the new window, select “Disable all macros except digitally signed macros” or “Enable all macros” option to your liking.
Step 3: Trigger Excel VBA Editor
Excel VBA editor is the place where you put the code. You can get access to it by 2 means.
Directly press “Alt + F11” key shortcuts to bring up “Microsoft Visual Basic for Applications” window.
Step 4: Add VBA Code
After entering Excel VBA editor, you need to add your code into either a project or a module.
1. Put Code into Existing Project
As you can see, under expanded “Microsoft Excel Objects” folder, you can see the existing projects, including the projects for each worksheet and the one for the current workbook called “ThisWorkbook”.
Then, put the code into it.
2. Put Code into Module
Besides, you can add code to a module by the following steps.
From the drop down list, select “Module”.
Then, in the new module, you can put your code.
Step 5: Run VBA Code
1. Run Macro in VBA Editor
2. Run Macro through Quick Access Toolbar (QAT)
Moreover, you can add macro to Quick Access Toolbar and run it through QAT by the steps below.
In the popup window, access “Quick Access Toolbar” tab.
Then, select “Macros” from the list of “Choose commands from”.
Next, choose the macro in the left side.
3. Run Macro through Ribbon
In addition, like Quick Access Toolbar, you can add and run macro in ribbon.
For a start, access “File” menu and hit “Options”.
In “Excel Options”, go to “Customize Ribbon”.
Then, create a new group on “Home” tab by “New Group”.
Optionally, you can rename this group by “Rename…” button.
Subsequently, select “Macros” in the list of “Choose commands from”.
After that, pitch on the desired macro in the left side.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf recovery and outlook repair software products. For more information visit www.datanumen.com
Bạn đang đọc nội dung bài viết How To Format Your Spreadsheets In Excel With Styles 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!