Previous article TipsMake introduced you to " Great tips excel part 1 " with the first trick, Today TipsMake please continue to share the " Great tips in excel part 2 " with the topic " Designing an excel spreadsheet yourself. pattern "
If you often have to perform the same layout for your spreadsheet, you can design your own sample worksheet, which already has a header, a number of columns, a number of certain rows, even names and formulas;It is also possible to create a separate dialog box into Excel's Templates Library.
Do you know if you often use templates?Instead of pressing Ctrl + N to create a new workbook, click the Office button, choose New [E2003: File |New], and select a pre-designed spreadsheet type in the Template library, then edit this workbook to your own?
Choose a pre-designed spreadsheet in Excel's Templates library
If you've ever seen or used a pre-designed spreadsheet, you'll find its usefulness, thanks to it, that saves us time in designing and presenting.
And, at work, if you often design spreadsheets of similar structure, do people often open an existing workbook, delete the content, and save it with a different name?But this way, sometimes by mistake, you will also delete the formulas that you have spent hours to devise, and then take time for deletion operations .
So, why not make one like the existing Excel templates?To whenever you need to type a certain worksheet (a payroll, a timesheet, a quotation, etc.), you just go into the Templates Library, even into the "My Library" of You, take out a sample then just input the data?Save a lot of time.
Create a sample spreadsheet
Templates allow you to have a "project" ready, which means you will have a spreadsheet available, including the worksheet's header, some rows and columns with its titles, tools, and more. formula, and Name (if any) . For example, this is the BillingStatement1 Template available in Excel 2007:
You can also create a similar one, then save it as a Template.This is not difficult.You open a new workbook, create a rib: color, format, name, design formulas, etc. as you normally do, but drill in.Please remove all unnecessary sheets, just keep the ones that have been designed.Or you can open an existing spreadsheet, delete all the data, just keep the presentation.Then, press F12 (Save as .) and select Excel Template:
Select the file type as Excel Template in the Save window
Give it a memorable name, then click Save.By default, Excel will save this template worksheet to the Directory containing the Templates that were created when you installed Excel 2007:
Excel automatically selects the folder to save the Templates
Whenever you need to use a workbook with the exact same structure as the one you just saved as the template above, click the Office button ➝ New [E2003: File |New], then select My Templates . in the New Workbook window:
Select My Template to open the saved template spreadsheets
Sample spreadsheets that you have created and saved as I have shown above will appear.Just select the template you need, then click OK.
Excel automatically selects the folder to save the Templates
Create a separate tab in the My Templates window
As you can see in the image right above, when you click My Templates in the New Workbook window, a New window opens.You will see this window in the form of tabs.But for now it shows it only in the My Templates tab.Assuming you have created lots of sample spreadsheets, instead of stuffing them all in one place, do you want to create separate tabs for each type of template?For example, tab "Tax report", "Quotes" tab, etc.?
When you press F12 to save as a Template as I showed above, instead of letting Excel choose the auto-save folder, click the Create New Folder button:
Click the Create New Folder button to create more tabs for the Template
A dialog box will open, and you enter a name for the form group you want.Here, I create another group called "Tax Report" to save the spreadsheets that will be used to print the reports submitted to the tax authorities, click OK, name the Template, and click Save.So that's it then!
You see, in this New window, there was a new tab called "Tax Report".Great is not it!
One thing to note, probably not redundant.All the templates and folders you created yourself in this article are here:
C: Documents and SettingsOwnerApplication DataMicrosoftTemplate
Owner is the name of the Acount you created when using windows.When selecting My Template in the New Workbook window, Excel lists only those templates that are located in the link above.
Therefore, when reinstalling Windows, you may lose everything in the C: Documents and Settings folder . So, the thing to note here is, you should create backup copies for These templates, carefully stored.Later, when you have to reinstall Windows, you can simply copy the copy into its correct place (the same path above), and you can use it again.