Don't Forget To add barbara@howtoexcelatexcel.com to your safe or contacts list to ensure you always receive your 3 FREE Excel Tips. |
| | | December 7 2016
Hello How
Welcome to the December 2016 edition of the How To Excel At Excel Newsletter. I hope you enjoy these next 3 tips which should get you flying though your Excel work, finish early and get to those festivities even faster.
Below are other places you can find and follow me for more Excel Tips on Social Media or download the FREE How To Excel At Excel App (IOS only) from the App Store for updates directly to your iPad or iPhone.
|
| |
|
| | | Your 3 Excel Tips Are- 1. Add Leading Zeros To Numbers Using TEXT Function
2. Hide Sheet Tabs In Excel
3. How To Automatically Open a Specific Workbook When You Start Excel
|
|
|
| | 1. Add Leading Zeros To Numbers Using TEXT Function.
| | In certain situations you may need to have data displayed with leading zeros for example customer records, or in my case I use store numbers in my databases and I add leading zeros to maintain data consistency for example 00564 and 03453.
I know I will never use these fields in any calculations, (I am never going to add the actual value of the store numbers together, or the actual number values of customer ID's), so maintaining he leading zeros gives a consistent data set. This proves very useful for using the value in VLOOKUPS or INDEX MATCH to find and fetch the details of my stores in my database.
So, there are few ways to do this but today let's look at one I do use a lot, and this is the TEXT function. Here is an extract from my data set. My database has a customer number with 5 digits. Some customers only have from 1 to 4 digits at present, but I want to maintain the 5 number format.
|
| | In column C which will be a helper column, so I select C3:C10, and type the following formula
=TEXT(B4,"00000")
Then hit CTRL+ENTER to auto fill the column of selected cells with the formula. This displays all of the Customer ID's with 5 digits, padding out the smaller ID's with zeros, until they all have 5 digits.
TEXT is a straightforward Excel Function. with 2x arguments
=TEXT(value format_text)
where value is a numeric value you want converted into text
and format_text is the formatting you want applied to the value
by using the formatting "00000" this forces a zero to be displayed in place of a digit.
here is the result of the formula being applied.
|
| | | | | 2. Hide Sheet Tabs In Excel.
| | By default Excel displays worksheet tabs which appear at the bottom of worksheets and allow users to navigate all through the available tabs if they want to. If you want to hide all of the worksheet tabs in Excel so users cannot probe around then then its really easy to do.
- File - Excel Options
- Advanced
- Display Options For This Workbook
|
| | All of the sheet tabs will now be hidden from view. So it looks like only the active sheet can be accessed. The Excel workbook looks somewhat strange at first without worksheet tabs, but some find this a lot less cluttered on the screen. Give it a try would you use this feature when issuing Excel Reports or Dashboards?
|
| | | |
|
|
|
|
|
| | | | 3. How To Automatically Open A Specific Workbook When You Start Excel.
| |
|
| | | If you always use a particular Excel workbook or Excel template, then automatically opening them every time you open Excel makes sense right?. I have a to do list in Excel which is open all day on my laptop as I update it multiple times every day. So, I have set Excel to open up my to list as Excel starts. Handy? It's really easy. Here is how.
First of all you need to find the XLSTART folder on your machine. It depends on what version of Windows you have but on Windows Vista and later it is usually here
Office 2013 – C:\Users\(User name)\AppData\Microsoft\Excel\XLSTART
It could also be here of you have upgraded from a previous versions of Windows
Office 2013 – C:\Program Files\Microsoft Office\Office 15\XLSTART
I found my Startup Folder by following these steps while in Excel
- File - Options - Trust Centre
- Trust Centre Settings
You will see a list of trusted locations, and in there will be the XLSTARTUP folder.
|
| | | Once you have located the folder just save the workbook or template to that location. Now when you start Excel, your chosen Excel workbook or Excel Template will open automatically.
|
| | | | | Let me know what you think of the Excel tips. If you want a tip mentioned in the Excel at Excel Newsletter- at the email address below.
|
| | | | | I really hope you enjoyed this edition of the How To Excel At Excel Newsletter. If you have any tips you want to share with other Excellers just email me- barbara [at] howtoexcelatexcel [dot] com and I will share with newsletter readers.
Keep Excelling!!
BJohnston MSc,Bsc(Hons),MCP,MOS
Don't forget you can follow me on Twitter for even more Excel Tips and Updates. |
| | The How To Excel At Excel Newsletter Is Available To Subscribers Only.
My records show that star.basak.howtodoit@blogger.com subscribed to this newsletter.
If you have been forwarded this newsletter or you would like your own copy then send a blank email to me at the address above to subscribe or visit www.howtoexcelatexcel.com and hit Sign Up.
|
| Copyright B.Johnston All Rights Reserved
|
| |
No comments:
Post a Comment
Note: only a member of this blog may post a comment.