Add time using Excel’s TIME() function | How to use Excel's TIME function to add time to time, with a worked example to create a series of appointments.
Add, move, group, copy, hide and protect Excel worksheets | How to add, move, group and copy worksheets within and between Excel workbooks, and how to navigate between sheets in large multi-worksheet file, and how to hide and unhide worksheets.
Calculate a RUNNING TOTAL in Excel | Sum the current value and all those that precede it in a column. This topic also shows how to fill cells down a column.
CHOOSE function with LINEST in Excel | Use functions CHOOSE and RIGHT to create an array of values which includes mixed data types, to feed a LINEST calculation
Comment in code: R, SQL, PHP, CSS, HTML, Stata | How to comment and uncomment in code in various software programs and languages, including R, SQL, CSS, PHP, HTML, and Stata. Examples and details on keyboard shortcuts, symbols, and how to comment and uncomment using the code editor Notepad++.
CONCATENATE in Excel | To join text and/or numbers together using Excel's CONCATENATE / CONCAT function. An example is shown how to generate a a set of SUM(CASE WHEN) SQL statements.
Create a DROPDOWN LIST in Excel | Create a list of items which can be selected from a dropdown selector in a cell in Excel, using data validation
Create a macro in Excel to sum a column | How to create a macro in Excel in 3 easy steps. The macro sums a column of values which can be set dynamically by referring to range using the INDIRECT() function. This method also shows how to use a dynamic formula which reads a value from the cell above the current cell.
Create, format, resize and rename Excel TABLES | Using Tables in Excel has many benefits (we've listed our 10 favourite reasons), and this topic page shows you how to create, format, resize, rename, add totals and summarize data, and use data entry forms with Excel Tables.
Customize the ribbon in Excel | How to customize the ribbon in Excel - add commands, create a new tab and group of commands in the ribbon
Day of the week from date in Excel | How to get the weekday number and day of the week from a date in Excel. Examples with syntax including how to use the WEEKDAY() and TEXT() functions and errors to watch out for.
Dynamic array of latest values from a table in Excel | In this detailed worked example, we show step by step how to retrieve a dynamic array of values, using a range of Excel's functions including OFFSET, INDEX MATCH, INDIRECT, and ADDRESS.
EVALUATE FORMULA in Excel to find errors | How to find errors in a complex formula, step by step, using Excel's EVALUATE FORMULA function. Where to find the command, method, worked example, and related topics.
Excel BEGINNER tips | If you're new to Microsoft Excel, or to any function software for that matter, there are a few basic approaches to take
Excel IF function | To test IF a value matches a condition, and return one result if TRUE, and another if FALSE.
Excel IFERROR then blank; IFERROR VLOOKUP | Excel's IFERROR function traps errors that could be returned from a formula or calculation, and gives you control over what is returned instead of an error message. Examples with syntax showing IFERROR then blank, IFERROR with VLOOKUP, IFERROR then perform another function.
Excel INDIRECT function | How to use Excel's INDIRECT function to refer to cells using the values entered into another cell. We show the basic method of how it works, and a more advanced example to refer to content in other worksheets.
Excel Sparklines — create, resize, customize, and clear | How to use Excel's Sparklines -- mini column, line and win-loss charts. See how to create and customize sparklines to show data trends, and how to clear or delete sparklines from a worksheet.
FORMULATEXT function in Excel – display formulas | It can be useful to inspect a cell's formula visually without having to click in it to display the formula in the formula bar. Excel's FORMULATEXT function enables you to see the formula in another cell.
Importing data – absolute and relative file paths in R | How to import data into R from files stored in local directories. Includes how to copy file paths in Windows, how to use relative and absolute file paths, and working in R Projects.
INDEX and MATCH in Excel | How to combine Excel's INDEX and MATCH functions to look up values. Syntax, examples and explanations of how to use INDEX and INDEX MATCH in Excel.
Keep track of rows in a knitting pattern in Excel | A basic worked example of how to create a row by row tick list for a knitting pattern, to keep track of rows and stitch counts. We use simple commands like copy and paste, flash fill, running total, rotate text and print preview to produce our knitting pattern instruction list.
Lambda functions in Python | In this post we describe the structure of lambda functions in Python, give examples, and discuss the pros and cons of using lambda functions.
LEAFLET map in R Shiny | Code to create a map in R Shiny using Leaflet. This example loads a base map, sets the centre and zoom, and has a reset button to return the map to the original view.
Make columns the same width in Excel | How to make all columns the same width in excel, by selecting a range of columns/rows, and dragging a column/row divider.
Nested IFs in Excel | How to write an IF statement in Excel, and nest IF statements to test for multiple conditions. Key tips and rules about IFs and nested IFs in Excel.
New website | Welcome to the new Excel Quick Help website giving free help for Microsoft Excel 2016!
PASTE SPECIAL with formulas | Add, subtract, multiply or divide a set of values by another value, using 'Paste Special'.
Power Query concatenate text and numeric data | How to concatenate in Power Query, combining text columns, and text with data columns. Steps in how to import data and enter formulas in Power Query.
Power Query dynamic Excel worksheet name | How to create a Power Query using a dynamic Excel worksheet name for the data source, with examples and guided steps.
Power Query dynamic file path | Use cell references in an Excel worksheet to create dynamic file paths for Power Query data sources. See how to name cells in Excel, and create custom objects in Power Query.
PROTECT an Excel 2016 workbook | How to protect your Microsoft Excel workbook so that users can only view the worksheets you want them to, and data and workbook structure can't be changed.
Python read, write and append text files | How to open, create, read, write and append to text files in Python. Code examples include how to read in text from one file and save to another file.
R Shiny selectInput dependent on another input | How to create an R Shiny selectInput control which is dependent on another select input. Two methods with code examples are shown: creating a reactive output variable, and using Shiny observe and observeEvent functions.
Remove BLANK CELLS in chart data table in Excel | How to remove blank cells from a set of data so that a plotted line is continuous. Example shows blank cells being replaced by 'N/A' values using an IF statement.
Shiny selectInput interactive controls in R | How to use Shiny's selectInput control in R, how to add to layout and add choices to the selection list, and how to use the outputs.
SQL CONCATENATE CONCAT | We show the basic syntax for SQL's concatenate function CONCAT, with examples on how to combine with other SQL functions such as SELECT, DISTINCT, and calculations. Worked example also shows how to create a temporary table.
Sum values if cells contain specific text in Excel | How to search a range of cells for specific text, and then sum another range of cells based on the search results. Worked examples using a combination of ISNUMBER, SEARCH, SUMPRODUCT, SUMIF.
Transform and plot data in R using plotly | How to transform data between wide and long formats, and plot the data in R using the base plot() function. Steps show how to import data using read.csv, replace the header column, convert wide data to long data, and plot the data.
VLOOKUP MATCH two-way lookup in Excel | How to use Excel's VLOOKUP MATCH functions to find values based on row and column lookups. The MATCH function offers several ways to create dynamic lookups, which can be easily copied across a range of cells in a worksheet.
VLOOKUP with CONCATENATE in Excel | Look up values in a table, using Excel's VLOOKUP with the CONCATENATE function to combine values in multiple cells for the lookup. For example, convert a long format data table to a wide format data table.
What is the difference between =, “<-" and "<<-" in R | An introduction to the different scopes of the assignment operators '<-', '=' and '<<-' in R programming, with examples of using the operators in the global environment and in functions.