The Cheat Sheet for your Spreadsheet: Mastering Contract Management with Excel

Excel spreadsheets are a popular tool for managing a contracts register for procurement and contract managers. Here are some key points to keep in mind when using Excel for this purpose:

Benefits of using Excel for managing contracts

• Flexibility: Excel allows you to customise the layout and structure of your contracts register to meet your specific needs. You can add or remove columns, use formulas to calculate data, and apply conditional formatting to highlight important information.
• Ease of use: Excel is a widely-used software that most people are familiar with, so there is a low learning curve for using it to manage a contracts register.
• Cost-effective: Excel is a relatively inexpensive software that comes pre-installed on most computers, making it a cost-effective option for managing contracts.

Best practices for using Excel for managing contracts

• Consistency: Establish a consistent format and naming convention for your contracts register to ensure that all data is entered consistently and can be easily searched and sorted.
• Data validation: Use data validation to ensure that data is entered correctly and consistently. This can include drop-down lists, data validation rules, and error messages to alert users when incorrect data is entered.
• Backups: Regularly back up your contracts register to prevent data loss in case of a computer crash or other issues.
• Security: Protect your contracts register with a password or other security measures to prevent unauthorised access or changes to the data.

Excel Cheat-Sheet and Hacks for Managing Your Contract Database

Excel is a powerful tool for managing databases, but it can be overwhelming to navigate all of its features and functions. Here are some cheat sheet hacks to help you manage your database more efficiently:

Formulas and Functions

• SUM – Adds up a range of cells
• AVERAGE – Calculates the average of a range of cells
• MAX – Finds the highest value in a range of cells
• MIN – Finds the lowest value in a range of cells
• VLOOKUP -Look up a value in a table and return a related value from a different column.
• INDEX MATCH -An alternative to VLOOKUP that provides more flexibility in data retrieval.
• COUNT – Counts the number of cells in a range that contain numbers
• COUNTIF/COUNTIFS – Counts the number of cells in a range that meet certain criteria
• SUMIF / SUMIFS: Sum values based on specific criteria.
• IF – Returns one value if a condition is true and another value if it is false
• VLOOKUP – Searches for a value in the first column of a table and returns a value in the same row from a specified column
• INDEX – Returns a value from a specified row and column in a table
• MATCH – Searches for a value in a range of cells and returns its position within the range
• CONCATENATE – Combines two or more text strings into one
• PROPER – Capitalises the first letter of each word in a text string
• TODAY – Returns the current date. Try subtracting the cell for “Today’s Date” from cells containing key contract dates (e..g. Expiry, Termination, Renewal). Doing this will give you the number of days from now until that date, and give you plenty of notice about upcoming activities.
• NOW – Returns the current date and time
• WEEKDAY – Returns the day of the week for a given date
• NETWORKDAYS – Calculates the number of working days between two dates
• IFERROR – Returns a value if a formula results in an error

Keyboard Shortcuts

• Ctrl + C – Copy selected cells
• Ctrl + V – Paste copied cells
• Ctrl + X – Cut selected cells
• Ctrl + Z – Undo last action
• Ctrl + Y – Redo last action
• Ctrl + F – Find and replace
• Ctrl + H – Find and replace with a new value
• Ctrl + A – Select all cells in the current worksheet
• Ctrl + Shift + L – Turn on/off filters
• Ctrl + Shift + : – Insert current time
• Ctrl + ; – Insert current date
• Ctrl + Shift + $ – Apply currency formatting to selected cells
• Ctrl + Shift + % – Apply percentage formatting to selected cells
• Ctrl + Shift + # – Apply date formatting to selected cells
• Ctrl + Shift + @ – Apply time formatting to selected cells
• Ctrl + Home  Go to the top-left cell (A1) of the sheet.
• Ctrl + Arrow Keys -Move to the edge of the data region in the respective direction.
• Ctrl + Shift + Arrow Keys -Select data in the respective direction.
• Ctrl + Page Up/Page Down -Move between different worksheets in the workbook.
• Ctrl + Shift + “+” -Insert rows or columns.
• Ctrl + “-“ -Delete rows or columns.

Tips and Tricks

• Use tables to organise your data and make it easier to sort and filter, Convert your contract data range into an Excel table for easier filtering, sorting, and formatting.
• Use the “format as table” feature to apply a table style to a range of cells and make it easier to sort and filter data
• Filter and Sort: Use “Data” > “Filter” to filter and sort data based on specific criteria.
• Use the “conditional formatting” feature to highlight cells that meet certain criteria or identify trends in your data, for example, to spot critical contract dates or values easily you might highlight deadlines less than 30 days in advance.
• Use pivot tables to analyse and summarise contract data dynamically.
• Use the “data validation” feature to create drop-down lists and restrict data entry to specific values and ensure that data is entered correctly and consistently, reducing errors
• Use named ranges to make formulas easier to read and understand
• Use the “freeze panes” ( Use “View” > “Freeze Panes” ) feature to keep headers visible while scrolling through large amounts of data
• Use the “text to columns” feature to split data into separate columns based on a delimiter
• Use the “remove duplicates” feature to remove duplicate values from a range of cells quickly
• Use the “fill handle” to quickly copy formulas and values to adjacent cells
• Use the “conditional sum wizard” to quickly calculate the sum of cells that meet certain criteria
• Protect Sheets: Protect critical sheets with passwords to prevent accidental changes.
• Keyboard Shortcuts for Quick Actions: Master essential keyboard shortcuts to speed up data entry and navigation – see our examples above for a headstart.

By using these keyboard shortcuts, formulas, and tips and tricks, you can manage your database more efficiently and effectively in Excel.

Potential limitations of using Excel for managing contracts

It would be remiss of us not to cover some of the potential limitations of using Excel for your contracts register:

• Limited scalability: Excel may not be the best option for large-scale contracts management, as it can become difficult to manage and search through large amounts of data.
• Limited collaboration: Excel is designed for single-user access, so it may not be the best option for teams that need to collaborate on contract management.
• Limited automation: While Excel allows for some automation through formulas and macros, it may not be as efficient as other software options that are specifically designed for contract management.

Conclusion

Overall, Excel can be a useful tool for managing a contracts register for procurement and contract managers, as long as best practices are followed and its limitations are taken into account.

Often, your spreadsheet will be the first place to start as the basis of your requirement when your register starts to become unwieldy to manage and share and you’re ready to move on to a database or system to help. Systems such as Accord, can replicate the fields in your contract spreadsheet as a starting point. Then, workflow and automation and ready-made reports and dashboards help to take your contract management to the next level.

Ready to take the first steps to gain control of your contract data? Download our free Excel contracts register template here.