Ready to chat data? That's what we're here for
Slicers act as visual filters, allowing you to filter and visualise your data as either in a table or as a chart. You can easily create Slicers in Excel.
2. Highlight your data in the table, click the design tab and then click insert Slicer.
3. Select the Slicer that you want to add and click OK.
4. The Slicer can then be used to filter your data.
5. If you add a chart you can also use the Slicer to filter the data in that.
Find out more about the services that Bespoke Excel provide, get in touch with our team of expert data security consultants today on 0161 883 2655.
In the example below there are 7 employees A to G and their monthly sales total. This is how you would round these numbers up to the nearest £1.
Using the formula =ROUND(C3/1,0)*1 you can easily round the monthly sales total to the nearest £1.
In excel you can use the round formula to round a number up or down to your desired amount, allowing you to optimise both time, and data management through cutting tedious tasks.
You can then apply this formula to the rest of the cells.
Being able to check that the criteria of data entered in to a cell range is useful function in Excel, but now you are able to determine the criteria in a number of cell ranges. In earlier versions of Excel, you can use the COUNTIF function to check a cell range for a single criteria, but from Excel 2007 onwards, you can use the COUNTIFS function for finding multiple criteria in a spreadsheet; proving that knowing your version of Excel can bring wonders to your data. The COUNTIFS function can be used to find the data that meets selected criteria in two or more cell ranges.
The syntax for this the Countifs multiple criteria function is ,
Countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The criteria in the example given are for sales of cars that have exceeded 10 in any given year. The criteria range 1 is B2 to B10 and the criteria is cars. The criteria range 2 is C2 to C10 and the criteria is greater than ( > ) 10. The function is written as,
=COUNTIFS(B2:B10,”cars”,C2:C10,>10). The returned answer in cell A12 is 3, there are three years that the sales of cars have exceeded 10.
Click on the formula tab on the Excel ribbon and then on the More functions button, from the drop down list choose statistical and then click on Countifs. When the Function arguments dialogue box appears you will have an area to enter the criteria range 1 and the same for the criteria. You will get then get the options for entering multiple criteria.
The formula you use to merge cells in excel is typing &” “& between each selected cells and then press Enter on the formula bar.
e.g =A1&” “&B1&” “&C1”
It is possible to merge cells in excel, combining the data from two or more cells into one. This is commonly used to enter a title along the top row of a spreadsheet. But this is also useful if you need to combine lists of data, like names and addresses into one cell. You should be aware that if you merge cells in Excel, only the data in the far left cell will be left in the new single cell, all the other data that were in the other cells will be erased, but there is away to work around this.
To put a title on your worksheet you enter the title in the top left cell in your spreadsheet and then highlight the number of cells that you want to make up the title. On the home tab of the ribbon there is the merge and centre button, if you click on the arrow to show the drop down menu, then click merge and centre. This will merge all the selected cells and centre the title.
To merge a number of cells into a single cell. You can use a formula that will do that for you. Before you use the formula you have to select a cell that you want to enter the data into.
Once you have merged the data in the first cells, you use the fill handle to complete the rest of the cells to be merged. Now, you will have the three original cells and a single merged cell with the data showing as a formula. You have to copy and use the special paste option to paste the data as a value. The original cells are no longer needed so they can be deleted, so now you just have the merged cells.
To calculate the Standard Deviation in Excel, you start by clicking on the formula tab on the ribbon and then click on the More Functions button. From the drop down list choose Statistics and then click on Std Dev button. When the Function Arguments dialogue box opens up, enter the cell range of the list of data that you want to use. Clicking OK will complete the calculation and give you the standard deviation in Excel for the selected list.
Standard deviation in Excel is used to calculate how far each individual data entered on a list deviates from the average of the list as a whole. It is not necessary to calculate the average of a data list to find the standard deviation as the formulas for calculating standard deviation as it is part of the formulas calculation. This formula has many different uses and using the tools in the formula tab on the ribbon makes it very easy to use.
A Gantt chart template for Excel will allow the user to schedule tasks in a specific project that will chart it from the start date to the completion of each task. A Gantt chart will make it easier to visualise a project from start date to completion, with each task of the project will have its own bar on the chart that will mark when it is completed. You can make a Gantt chart as simple or as complex as you require, but once you have created your first you can save it as a Gantt chart template in Excel.
You start by creating a table that will list all the task in the project from beginning to end, then you add the starting date for each task, the duration and then the completion date.
Once you have completed the table, you need to create your chart. Click on the insert tab and then on bar chart. From the bar chat options, choose the stacked bar. Click on the select data button and then enter the data series from your table. Now your stacked bar chart represents each task with its start date, duration and completion date. Now you can format your chart.
The first thing you will notice is that your tasks are listed in reverse order, to change this click on the list of tasks on the axis to highlight them and then right click to bring up the Format axis dialogue box, then in axis options tick the Categories in reverse order box.
You now want to delete the starting date part of the bar chart. Right click on that part of the bar and that will bring up the Format data series dialogue box. In the Fill section, click on No fill, then on Border colour click on No line. Now you can get rid of all the white spaces in between the bars by right clicking on the top bar to bring up the Format series dialogue box and then on series format set the gap width to 0.
You can now save this as a Gantt chart template in Excel for you to use on any other projects you have. There are many more sophisticated Gantt charts templates that you can download that will allow you to use on more varied and complex projects.
