Creating a scrollable chart is a great trick for enhancing the functionality of your Excel dashboards. You may have years of data to display in the chart, and yet space is limited on your spreadsheet.
By adding a scroll bar to the chart, users can interact with the chart and scroll to see the data they want displayed.
A Histogram is used in statistics to graphically represent the distribution of data. It looks like a column chart with each column representing an interval (bin), and the column height representing the frequency that it appears.
Essentially the graph groups numbers into intervals (bins) and displays how often they appear. The graph then beautifully illustrates how the sets of numbers are distributed.
If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.
Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.
I have started to learn German as it has always been a goal of mine to learn the language. So I started to list some vocab in Excel to test myself.
I am using an English keyboard and the German language contains 4 special characters that do not appear on an English keyboard. You have the 3 umlaut accented characters ä, ö, ü and the ß (esszet).
There are two main ways to enter these letters into Word or Excel. One way is to insert the symbol, and the other is to use the number code associated with each character.
Your spreadsheet formulas are always shown in the Formula Bar of Excel. This makes it easy to view and edit the formulas of a spreadsheet.
But what if we have created an Excel file and we do not want others to be able to view the formulas. Prevent formulas showing in the Formula Bar by setting them to hide and applying worksheet protection.
Hyperlinks can be used to link to other sheets, webpages or other files such as PDF’s from your Excel spreadsheet. If you are creating hyperlinks for many records though this will take a long time to set them up.
Excel provides a HYPERLINK function for creating hyperlinks in our spreadsheets. The real power behind this function is that it can be used to create dynamic hyperlinks.
We can create conditional hyperlinks by nesting them within an IF function, or create hyperlinks that can find the address to link to themselves by embedding Lookup or Text functions within them. This helps us to create automated and error resistant spreadsheets.
Do you want to advance your skills by learning Excel? Performing date calculations can sometimes be troublesome. Trying to calculate the number of working days between two dates, or automatically finding the date in two months’ time is not as straight forward as formulas with general numbering can be.
Fortunately Excel has a full repertoire of fantastic date functions. Here are five of the best.
Timeline Slicers are a new addition to Excel 2013. They are a new filter tool that is available exclusively to the date fields in your PivotTables. They are visually appealing and easy to use.
To use a Timeline with your PivotTable;
In the latest version of Excel it has become your responsibility (if you choose to) to create your own toolbar, and in our basic Excel series we show you how.
The Quick Access Toolbar at the top of your screen begins with just the Save, Undo and Redo buttons. If you require fast access to any other Excel commands you are able to add them yourself. You can add almost any functionality to the toolbar including those not found on the Ribbon.
As part of our basic Excel series, this post explores 7 incredible additions to the toolbar that are sure to improve the way you work.