7 Excellent Ways to Excel at Excel

7 Excellent Ways to Excel at Excel Microsoft Excel is packed with useful functions that can help to speed up day to day jobs  in the workplace, however for  a lot of us the program is a total mystery and completing the simplest of tasks can leave us in a meltdown. If youre not quite as clued up with Excel as youd like to be, this handy guide from  Best STL  may be of some help. Master  these 7 easy to learn tricks and youre sure to impress your boss! 1) Flash fill One thing that makes Excel great, is that its pretty good at picking up on patterns in what you enter and how you edit data. If youre always having to split up first and last names in your database, or switching between UPPER and lowercase, Excel can pick up on this and make the corrections for you automatically. How: You can do this by clicking in the column next to the data you want to fix and typing how you would like it to look. Then select the correction and all of the other cells you would like to correct and hit CTRL+E. 2) Pivot tables Pivot tables allows you to quickly summarise large amounts of data in lists or tables without having to use formulas what a life-send! How: All you have to do is click in your table of data and go to InsertPivot table. Once youve highlighted all of the data you want to analyse, hit OK. You can use the right hand bar to to create your table by dragging the fields in the top half to their respective slots in the lower four boxes. 3) Goal seek If you know what answer you want to get from your data, but youre not sure will inputs will produce that answer, Goal Seek can help you. By letting it know what you want answer youre after, it will tell you what figures you need to put into your formulas to produce the result you are looking for. This is a useful tool to use for forecasting and setting goals. How:  You can do this by going to DataData ToolsWhat-If AnalysisGoal Seek. In the Set Cell box, select the cell with the formula you want to determine. On the Top Value box enter the answer you need. Lastly use By changing cell to pick the input value that Excel can adjust, then click OK. 4) Conditional formatting So that results can be read at a glance when reviewing spreadsheets, conditional formatting can be used to highlight key information. This is great for reporting and analysis. How: Highlight the cell range by going HomeConditional Formatting and choose from the range of formats available. For example, the to highlight all values lower than 100, choose Highlight Cell RulesLess than and type in 100. Before clicking with OK you can also choose the format that will apply to any matching values. 5) Index and Match If youre looking to search through data in your table you can use Index and Match, which offers much more flexibility, speed and ease than VLOOKUP. This is really handy for retrieving data from a complex  sets of data. How:  Ensure that your data is formatted in a grid with column and row labels. Use Match to return the Column that contains your search target and another Match to find the row that will contain your answer. Feed these two answers into Index and Excel can find the value where the two intersect. 6) Waterfall charts Waterfall charts are on the most popular options for visualisation of data. They break down net values into flow and contributions to a total. This is particularly useful for reporting performance. How: Select InsertInsert Waterfall Chart or Stock chartWaterfall. 7) Forecasting Forecasting allows you to predict future outcomes from historical datasets. This is great for modelling performance expectations. How: You can do this by selecting the corresponding sets of data and go to DataForecast Sheet. In Create forecast Worksheet box choose either a line chart or a column chart, pick and end date and then select Create. Excel tricks that will impress your boss by Excel Training