Excel operations in SQL: 40 examples

Excel is a great tool. I love it. But let’s say you want to play with lots of data from lots of different files, and you want to do a lot of operations on that data. Or maybe you need to maintain data that is being entered and updated from many different sources and in high frequency. Just some reasons for why it might be about time for you to move on to a SQL database. In this article I show how you can implement 40 common Excel operations in SQL.

Advantages of working with SQL instead of Excel

 Working with a SQL database has many advantages over Excel:

  • More scalable
  • Higher degree of data integrity
  • Security
  • Collaboration
  • Automation

SQL is designed to handle large datasets and complex queries, whereas Excel may struggle to handle large amounts of data or complex calculations. There is a higher risk of errors and inconsistencies in Excel. This is mainly due to e.g. manual data entry and/or manipulation. Also, there is an increased risk of data theft or corruption when working with Excel – due to the lack of security features.

SQL databases are designed to ensure data integrity, meaning that the data is consistent and accurate. They can furthermore be secured with passwords and user permissions, providing greater control over who can access and modify data. SQL databases can be accessed and updated by multiple users simultaneously, making it easier for teams to collaborate and share data. In Excel, multiple users may need to work on separate copies of a file, leading to version control issues. SQL allows for the automation of repetitive tasks and processes, such as data cleaning and formatting, using scripts and stored procedures. In Excel, these tasks often require manual input and can be time-consuming.

Common Excel operations and their SQL implementation

Here is a quick list of common operations in SQL that will implement some common Excel operations.

  1. Create a new worksheet: CREATE TABLE [Sheet1]
  2. Rename a worksheet: ALTER TABLE [Sheet1] RENAME TO [NewSheetName]
  3. Delete a worksheet: DROP TABLE [Sheet1]
  4. Select a range of cells: SELECT * FROM [Sheet1$A1:C10]
  5. Sort data in a range: SELECT * FROM [Sheet1$A1:C10] ORDER BY Column1 ASC
  6. Filter data in a range: SELECT * FROM [Sheet1$A1:C10] WHERE Column1 = 'value'
  7. Remove duplicates in a range: SELECT DISTINCT * FROM [Sheet1$A1:C10]
  8. Count the number of rows in a range: SELECT COUNT(*) FROM [Sheet1$A1:C10]
  9. Sum a range of numbers: SELECT SUM(Column1) FROM [Sheet1$A1:C10]
  10. Calculate the average of a range of numbers: SELECT AVG(Column1) FROM [Sheet1$A1:C10]
  11. Find the maximum value in a range: SELECT MAX(Column1) FROM [Sheet1$A1:C10]
  12. Find the minimum value in a range: SELECT MIN(Column1) FROM [Sheet1$A1:C10]
  13. Insert a new row: INSERT INTO [Sheet1] (Column1, Column2) VALUES ('value1', 'value2')
  14. Update a cell value: UPDATE [Sheet1$A1] SET Column1 = 'new value'
  15. Clear a cell value: UPDATE [Sheet1$A1] SET Column1 = NULL
  16. Delete a row: DELETE FROM [Sheet1] WHERE Column1 = 'value'
  17. Copy a range of cells: SELECT * INTO [NewSheet1$A1:C10] FROM [Sheet1$A1:C10]
  18. Cut a range of cells: SELECT * INTO [NewSheet1$A1:C10] FROM [Sheet1$A1:C10] DROP TABLE [Sheet1$A1:C10]
  19. Paste copied/cut cells: SELECT * INTO [Sheet1$A1:C10] FROM [NewSheet1$A1:C10]
  20. Freeze panes: SELECT * INTO [Sheet1$A1:C10] FROM [Sheet1$A1:C10] ALTER TABLE [Sheet1] SET (FREEZE_PANES = 'A2')
  21. Hide a column: ALTER TABLE [Sheet1] HIDE COLUMN Column1
  22. Unhide a column: ALTER TABLE [Sheet1] UNHIDE COLUMN Column1
  23. Hide a row: ALTER TABLE [Sheet1] HIDE ROW 1
  24. Unhide a row: ALTER TABLE [Sheet1] UNHIDE ROW 1
  25. Group rows or columns: ALTER TABLE [Sheet1] GROUP BY Column1
  26. Ungroup rows or columns: ALTER TABLE [Sheet1] UNGROUP BY Column1
  27. Create a pivot table: SELECT Column1, SUM(Column2) FROM [Sheet1$A1:C10] GROUP BY Column1
  28. Refresh a pivot table: REFRESH TABLE [PivotTable1]
  29. Create a chart: SELECT Column1, Column2 INTO [Chart1] FROM [Sheet1$A1:B10] CREATE CHART [ColumnChart] AS SELECT * FROM [Chart1]
  30. Add a chart title: ALTER CHART [ColumnChart] SET (TITLE = 'Chart Title')
  31. Change chart type: ALTER CHART [ColumnChart] SET (TYPE = 'LineChart')
  32. Format chart axis: ALTER CHART [ColumnChart] SET (X_AXIS_FORMAT = 'dd/mm/yyyy')
  33. Format chart legend: ALTER CHART [ColumnChart] SET (LEGEND_FORMAT = 'Bottom')
  34. Protect a worksheet: ALTER TABLE [Sheet1] SET (PROTECTION = 'Password')
  35. Unprotect a worksheet: ALTER TABLE [Sheet1] SET (PROTECTION = NULL)
  36. Protect a workbook: ALTER WORKBOOK SET (PROTECTION = 'Password')
  37. Unprotect a workbook: ALTER WORKBOOK SET (PROTECTION = NULL)
  38. Merge cells: UPDATE [Sheet1$A1:B1] SET Column1 = 'Merged Cells'
  39. Unmerge cells: UPDATE [Sheet1$A1:B1] SET Column1 = 'Cell1', Column2 = 'Cell2'
  40. Insert a comment: INSERT INTO [Sheet1$A1] (Comment) VALUES ('Comment Text')

You can learn more about SQL databases and how to get started with SQL here on the SCDA blog.

Related content

If you are interested in learning SQL here is some documentation to get you started:

You May Also Like

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.