Don't Forget To add barbara@howtoexcelatexcel.com to your safe or contacts list to ensure you always receive your 3 FREE Excel Tips. |
| | | July 6 2016
Hello How
Welcome to the July 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. Easily view your formulas with FORMULATEXT function
2. Don't Have Much Space?- Use Sparklines For Charts
3. Did You Know You Can Move The Formula Help Box????
|
|
My Recent Blog Posts You Might Like-
|
|
|
| | 1. Easily View Your Formulas With FORMULATEXT Function.
| | This handy function is available from Excel 2013 onwards, unfortunately it is not available for earlier versions of Excel. Sorry guys.....
So what does it do?.
Simply put it returns a formula as a text string.... Or in other words it writes your formula in a cell as a simple text string- exactly what you see in the formula bar if you click on a cell that contains a formula. This feature is really useful to be able to inspect exactly what your formulas look like/how they are written.
What is the FORMULATEXT Syntax?
This is faily easy, it it
=FORMULATEXT(reference)
Where
Reference - this is equired. It can be a reference to a cell or range of cells.
Here is an example. I have two formulas in my Excel worksheet in cells B5 and B6. I have the FORMULATEXT Function set up in cells C5 and C6, which returns the formula details as a text sting from cells B5 and B6.
You see how handly this can be to check your formulas that are running in your worksheets?.
|
| | As well as referencing cells in the same worksheet, you can refrence cells in another worksheet or workbook. If you are referencing cells in a closed workbook then Excel will display the #N/A error.
You will also see the #N/A error if
- the formula on your cell is longer than 8192 characters;
- the reference cell has no formula in it;
- or if there is worksheet protection that prevents a formula being shown.
You will see a #VALUE! error if the cell you are referencing contains an invalid data type
Give this a go.
|
| | | 2. Don't Have Much Space?. Use Sparklines.
| | If you don't have a lot of space on your Excel worksheet or dashboard then using Sparklines are a great way to have some tiny charts (as in the example below) to show trends in your data. The sparkline charts below are contained in one cell, they are not objects like usual charts in Excel but are tiny chart in the background of a cell.
Let's take it step by step to create these tiny charts, great for when you have little space.
|
| | You can download the example data from here to work along if you want to.. My data example is Monthly Sales per Sales Region.
|
| | - Select a cell where you want your sparkline to sit.
- Insert Tab - Sparklines Group - Select the type to create- I used COLUMN.
- In the Data box select the cells you want to use for the data for your chart.
- Hit Ok. You have your basic Sparkline Chart.
- We want to make this chart a bit more informative to our audience so there are a few extra steps to really make it pop.
- Set the Cell Height to 42(56 pixels) or whatever height you like the look of, as the chart auto adjusts as you increase or decrese the cell height.
|
| | - In the cell above the chart, reference the Region Cell that your sparklines chart refers to, for example East Sales it will be =A2, for West Sales =A3 etc etc.
- In the cell below the Sparklines Chart, but in the same column as your charts type the values that represent the months in this case JFMAMJJASOND. I have spaced these with 1 character between each of the labels.
- The font for these are COURIER NEW 8 pt.
- Expand or contract the width of the cell, the points will align and you are good to go.
|
| - To make your high and low points in the chart (if you want to), select the cells that make up your Sparkline chart/s then select - Desgin - Style - Marker Colour.
- Select your High Point and Low Point Colours, in my example I have chosen Green and Red.
|
| | - To finish off the chart there are a couple of Axis settings- Sparkline Charts- Sparkline Tools - Design - Group - Axis - Horizontal Axis Options - Show Axis.
- Then in the same menu select Vertical Axis Minimum Value options - Custom Value - Set this to 0.00 so all of the charts begin at 0.00 on the Vertical Axis.
That's it save some real estate on your Excel worksheets and create some awesome tiny charts.
|
| | | | |
|
|
|
|
|
| | | | 3. Did You Know You Can Move The Formula Help Box?.
| |
|
| | | Does the Formula Help Box somtimes get in the way when you are writing formulas?. Yes it is annoying and useful at the same time. I use it a lot as it describes the syntax of the formula which is a handy guide as you are building your formula.
|
| | So, just grab the Formula Help Bar and drag it out of the way! You can now continue your formula creations without it covering up parts of your worksheet you need to access.
|
| | | | | | 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.