Don't Forget To add [email protected] to your safe or contacts list to ensure you always receive your 3 FREE Excel Tips. |
| | | November 2 2016
Hello How
Welcome to the November 2016 edition of the How To Excel At Excel Newsletter. I hope you enjoy these next 3 tips.
You can download the examples used in this newsletter here and follow along with the tips.
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. Make Your Excel Charts Better With this Chart Visualisaton Tip.
2. Save Your Excel File Automatically With Today's Date. (A Simple Macro).
3. Force Your Excel File TO Open In Normal View.
|
|
My Recent Blog Posts You Might Like-
Find And Hide All Formulas On An Excel Worksheet
Formula Friday – 2 Ways to Switch First And Last Names In Cells In Excel
Formula Friday – Auto Sum Packs A Punch With This Cool Trick
|
|
|
| | 1. Make Your Excel Charts Better With This Chart Visualisation Tip
|
| One of the parts of Excel charts I struggle with is the balance between getting across the meaning of the chart and how much chart junk you need on the chart to convey this information.
That's why this is a really cool trick that allows you to include the data detail you would normally include if you decide to include Data Label in your Excel chart, but not directly on your chart avoiding cluttering it up.
|
| | So, what is the secret to this?, it's actually combining the X Axis labels, a LINE BREAK and then a reference to the actual data values we used in the Excel chart.
How Does This Work?
We use the ANSI code that is associated with every character in Excel. This is just the Windows system code that determines what characters you see on your screen, for example just type =CHAR(55) into Excel and it will display 7. Give a few more numbers a go, you have 255 to play around with!.
So ALL characters have codes, even a space is 32 and a line break which is what want for our solution is actually coded as 10. So, we can create a rather clever X Axis label with the following formula
=B2&CHAR(10)&D2
|
| | Handy isn't it? The cell in your data area will not show the line break unless you apply text wrapping to the cell, but the line break will appear in the chart. Give it a go.
|
| | | | 2. Save Your Excel File Automatically With Today's Date (A Simple Macro)
|
| This tip is one of my Excel Macro tips I share every Monday in my Macro Mondays blog post series, I had a lot of good feed back since publishing it so have decided to share it in my Monthly Excel At Excel Newsletter. I hope you like it.
What Does The Macro Do?
So, this simple Excel Macro will save a new copy (or backup) of the file you are working on with today's date added to the filename. It is really handy and I use it a lot especially with files that I use on a regular basis and just update week after week.
How Does The Macro Work?
There are a few clever pieces of this Macro, and they combine the filepath, today's date and the original filename you have used for your workbook. There is actually only one, yes ONE step to this awesome Macro. Excited? let's get started!….and code.
FIRST Create A Module. Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic. Click Modules, and Insert New Module. This Macro is saved in a Module.
Step 1. (Yes the only step- but we will talk through the coding the Macro).
So we kick off with the file path being created using the Path property of ThisWorkbook object and today's date is created by the use of the Date function.
|
| | We have to format the date with hypens "-" as the default date is saved with back slashes……but if you have used windows for any length of time you will know that you can't use back slashes in filenames. That's ok though we can reformat it – not a problem.
The final part of the new filename is it original filename you have used to save your Excel workbook. Here we use the Name property of the ThisWorkbook object to capture.
Oh there is one last step.......
Step 2. - Test your Macro!
Here is the code if you want to copy and paste it into Excel.
Sub SaveCopyOfMyWorkbook()
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & _
Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.Name
End Sub
Let me know if you use this Macro.
|
| | | |
|
|
|
|
|
| | | | 3. Force Your Excel File To Open In Normal View.
|
|
|
|
|
| There is a large chance that you have experienced an Excel workbook with Protected View enabled. Even on your own files, Excel can appear to be preventing you from opening them, but it is just protection from malware, to ensure there are not harmful contents in the Excel file that execute when you open an Excel file.
You will know if you have tried to open a file with Protected view as you will see the Yellow coloured warning that looks like the screen shot below.
There could be numerous reasons why you get the Protected View warning including-
- The file was opened from a location on the Internet
- The file was opened from an unsafe location
- The file was attached to an e-mail message
- You chose to open the file in Protected View
In my example seen below it was warning regarding a file that has been downloaded from the internet.
|
| | If you are certain that a file is to be trusted - for example a file that is regularly received via email from one of your colleagues then you can force a file to open in Normal View. There are just a few steps.
- Right Click the workbook name (or it's Icon) and select properties fro the shortcut menu
- In the Properties Dialog Box select the General Tab
- Click Unblock
- Click Ok to close the Properties Dialog Box again
|
| | | After you have carried out these steps your workbook will open in Excel in Normal View not Protected View. |
| | |
| | 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.
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
|
|
|
